Using hyperlinks for navigation in Excel

So your new workbook needs some navigation links. You may want to use hyperlinks, because most people are familiar with them, thanks to the internet, but they create a couple of little problems we need to solve. The example workbook here (Hyperlinks.xls) illustrates these problems and their solutions.

First - creating a hyperlink 

First, to create a hyperlink, select the cell containing the link text, select Insert, Hyperlink, choose "Place in this document" on the leftof the dialog, and then choose the sheet and enter the cell reference.

You can also use a textbox,which lets you put the hyperlink anywhere,and not just in a cell. Right click on the textbox and chooseHyperlink. You'll need to format it, to remove the border,and change the text to underlined blue, so it looks like a hyperlink.

Problem - hyperlinks break easily

Suppose you point your hyperlink at cell C25, and later, you insert a row higher up. You would expect the hyperlink to now point at A26, but it still points at C25, *sigh*.

The answer is to create a range name for C25, and point the hyperlink at the range name. Then it will update correctly.

Problem - Excel shows the wrong cells

 Suppose we want to link to a table starting at C25 onwards. The hyperlink will show you C25, but it may be at the bottom of the screen,with most of the table hidden. What happens depends on whether you were above, below, to the left or the right of the linked cell.

So how do you get Excel to show the right cells, every time? The simple answer is to link to more than one cell. Suppose you have a set of tables down the sheet,so we are navigating up and down between them.

I insert a new column (A) at the left and then hide it. Suppose I am linking to a table starting at row 25. I point the hyperlink to A25:A200 (say), so that Excel can't fit them all on the screen. What it does is put the top left linked cell, ie A25, at top left of screen, and you will get the full table showing. It will do this regardless of whether you were above or below the table when you clicked the link. Note that I hide the new column A so the user doesn't see all the cells being selected.

You can do this with rows as well, if you are navigating from left to right. (Don't ask me about navigating both across and down in one sheet - I don't mix them).

Problem- you need a dynamic (changing) link 

 Suppose you want a link to go to different places depending on what the user has chosen. You can do this dynamically, as the example file above shows.

What I use

 I started programming way back,when we used buttons for everything, including navigation. Now, however, buttons are used on the internet for actions (eg submitting forms) and hyperlinks are used for navigation. So I use hyperlinks, mostly.

Finally, please look at the example file: Hyperlinks.xls