Hyperlink to jump to another cell in Excel
I am using MS Excel 2007 (previously familiar with Excel 2003). I create a simple hyperlink in cell A1 (in sheet Sheet1) so when I click cell A1, the cursor moves to cell B10. It's very easy, no problem with that (right click in cell A1, choose hyperlink and so on).
The problem arises when I rename the tab, the hyperlink won't work, warning "Reference is not valid", then I have to edit the hyperlink to make it work. The problem also arises when I copy the sheet (new sheet named Sheet1 (2)), if I click cell A1, it directs me to cell B10 in Sheet1 instead of cell B10 in the new sheet (Sheet1 (2)). Again I have to edit the hyperlink to make it work.
Is there a way to prevent me from editing the hyperlink each time I rename the tab or copy the link to another sheet (make it 'relative' instead of 'absolute')?
I don't think you can do it without a macro. The standard hyperlink function of Excel is designed to work with given Workbook, Sheet and Cell references. Yet, it could be more comfortable for you to use HYPERLINK formula. Typing the following formula in cell A1 will create a hyperlink which will take you to cell B10 when clicked:
=HYPERLINK("[Book1]Sheet1!B10", B10)
When you change the name of Sheet1, then you should change the content of this formula too.
=HYPERLINK(CONCATENATE("#",CELL("Address",A1)),"Link Name")
the "#" for some reason represents the current filename (I lifted that off google at one time). The whole thing can be pretty flexible and you can get sheet and file names dynamically via formula if you are someone who re-arranges your files a lot.
I use dynamic hyperlinks; That find whatever I match in Z15 and set me a link to it.
=HYPERLINK(CONCATENATE("#",CELL("Address",INDEX($A$1:$A$2500,MATCH(Z15,$C$1:$C$2500,0)+0))),"Link Name")
The other hyperlink functionality (right click on cells) is fairly cumbersome.
... Most of my documents nowdays have a table of contents sheet full of hyperlinks that switch me to all the key points i need to go between. The rest just have a few frozen rows or columns used for sheet wide nav and a link back to the table the contents.