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')?
Best Answer
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
HYPERLINKformula. Typing the following formula in cell A1 will create a hyperlink which will take you to cell B10 when clicked:When you change the name of Sheet1, then you should change the content of this formula too.