When using a hyperlink in Excel (2007) to go to another cell (in either the same or a different worksheet but in the same workbook), is there an easy way to consistently have the target cell end up in the upper left corner of the screen (Excel window)?
As it is now when using a hyperlink, the target cell ends up being positioned in one of the four corners based on the location of the last active cell in that worksheet.
For example, suppose that when a maximized Excel worksheet is on your monitor, you can see columns A-Z and rows 1-50. Furthermore, suppose you have a table of data, 50 rows tall and Z columns wide with the upper left corner of the table located at CA501 (yellow cell in Fig. 1). That would mean that the table range would be CA501:CZ550 (blue area in Fig. 1) and just fit on your screen.
Now suppose that you want to be able to quickly get the complete table on your screen from many different locations in your workbook. Obviously, the simple solution is to select the full range (CA501:CZ550) and give it some name which then can be the target for any number of hyperlinks throughout the workbook.
However, for reasons I will not go into, suppose that you cannot define the entire range but only the single cell in the upper left corner (CA501). With this being the case, your hyperlinks target only CA501 and you may or may not end up with the table displayed on your screen.
For instance, suppose your last active cell in that worksheet is A1 (a position up and to the left of your target cell). When the hyperlink is activated, it only moves you to where the target cell, CA501 (yellow cell in Fig. 2), just barely shows up at the bottom right corner of your screen. Thus, your screen displays a range of cells up and to the left of the target cell (green area in Fig. 2) but none of the table can be seen.
Similarly, if the last active cell is up and to the right, hyperlinking results in the target cell appearing in the bottom left corner of your screen and the range displayed on your screen is up and to the right (red area in Fig. 3). You might see the top row of your table at the bottom of your screen but the rest of it is off-screen below.
The table is correctly and fully displayed on the screen only when the hyperlink "jump" comes from a last active cell which is located down and to the right of the target cell. Hence my initial question: "Is there an easy way to consistently have the target cell end up in the upper left corner of the screen (so the range down and to the right is what appears on the screen)?
The only solution I've thought of is to utilize the following steps:
1. name some cell (let's call it "BOUNCE") way down and to the right (that will never be used for anything else in that worksheet and will always be below and to the right of any data)
2. create a macro which always jumps to the named cell first and then from there, jumps to the target cell.
An example of where this approach has been very effective is in a directory I created with several hundred names sorted alphabetically. I first divided the directory into groups -- one for each letter in the alphabet -- and gave each group a header row with the first cell in each header having the letter of that group (e.g. - A -, - B -, -C-, and so on).
Next, from the Insert menu, I chose a button shape which I duplicated 26 times and gave each one a letter. Finally, each button was assigned a macro which jumped first to the BOUNCE cell and then to the desired letter. Because the second jump is always coming from down and to the right, the targeted header always appears in the upper left corner so that the screen is filled with only those names beginning with that letter.
The buttons are arranged side-by-side in three rows so any time I find myself working in one specific worksheet for an extended period of time, I can simply copy the 26 buttons from my directory and paste them wherever I want and in so doing, be only a single click away from a desired letter or group of names in my directory.
Now though, I've got a much, much larger project with far more "bounce" links to create and it would sure be nice to get the same result without having hundreds or even thousands of macros.
It would be great if anyone has a suggestion that could simplify what I have described.
Incidentally, another part of this question is described here: Run Excel macro by clicking a designated cell