Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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')?

Thanks, Andy Aldy

share|improve this question

1 Answer 1

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.

share|improve this answer
1  
To extend upon this, if the links are going to always be within the same sheet, you can have a cell on the sheet that you type the sheet name into, and then build every HYPERLINK formula on the sheet using that. You'd end up with something like this: =HYPERLINK("[Book1.xls]"&A1&"!B1","Up") where in A1 you type the name of the cell. Then you only have to change one cell to update all the hyperlinks, but if you've only got one HYPERLINK formula on each sheet this won't help. –  DMA57361 Jul 22 '10 at 14:45

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.