Automatically updating hyperlinks based on tab names

[danielstone121]

Hey everyone-

Okay, so I'm sorry if this isn't very clear, but I am working on a workbook that is going to use several worksheets that are very similar in the information that they hold. Each one will have a unique name but that name may change. I want each worksheet to contain a list of hyperlinks that will help users quickly navigate to different sections within in the same worksheet (each sheet is pretty large with about 5 or 6 unique sections of information). The problem I am coming across is that with simple hyperlinks every time the worksheet names are changed users get an "Invalid Reference" error when they try to use the hyperlinks.

How do I create hyperlinks that will automatically be updated when a worksheet is renamed? And, if you can tell me, why on earth don't they do that already?

Please let me know if you need more information. Thanks!

 

[HRIS]

Hello Daniel. If you set up the hyperlinks to named ranges instead of just sheet references, it should not break when the sheet name changes.

 

[HRIS]

In case it is useful, to name a range you just select the range you wish to use/hyperlink to, type the name in the field to the left of the formula bar (just above columns A & B), and enter. Then when you insert hyperlink and choose "Place in This Document" you should be able to select Cell Reference or Defined Names - select the named range you just defined.

 

[danielstone121]

In case it is useful, to name a range you just select the range you wish to use/hyperlink to, type the name in the field to the left of the formula bar (just above columns A & B), and enter. Then when you insert hyperlink and choose "Place in This Document" you should be able to select Cell Reference or Defined Names - select the named range you just defined.

Thank you for your response! I think that would work for existing worksheets, but what if I have to add new worksheets? In other words, if I have to create a copy of an existing worksheet will the new worksheet link to the original worksheet's named range? It seems like it would, but I'm not sure.

I need to be able to create copies of the existing worksheet that will also have automatically updated hyperlinks. Does that make sense?

 

[HRIS]

If you are copying the sheet that displays the hyperlink, then the link on the new sheet should still take you to the original named range location. So if my name range is on "sheet 1" and my link is on "sheet 2" and I create a copy of sheet 2 that is named "new sheet 2", I should be able to click on the "new sheet 2" link and it would still take me to the named range on "sheet 1." Is that what you were needing or am I misunderstanding?

 

[danielstone121]

If you are copying the sheet that displays the hyperlink, then the link on the new sheet should still take you to the original named range location. So if my name range is on "sheet 1" and my link is on "sheet 2" and I create a copy of sheet 2 that is named "new sheet 2", I should be able to click on the "new sheet 2" link and it would still take me to the named range on "sheet 1." Is that what you were needing or am I misunderstanding?

You are understanding, and again, I really appreciate the help, but that is what I don't want to happen. Each worksheet needs to have unique hyperlinks to cells on that same worksheet. So, for worksheet 1, the links need to be able to take me to cells U1, BF1, BP1, CA1, and CF1, all on worksheet 1. For worksheet 2, I need to link to all of those same cell names but on worksheet 2, not worksheet 1. I will then also need to be able to create a copy of worksheet 2 (and say I name it worksheet 3) and have my hyperlinks connect to cells U1, BF1, BP1, CA1, and CF1 but now on worksheet 3, not worksheet 2.

So, though I might not be using the right terms, I need hyperlinks that will dynamically update to link to cells U1, BF1, BP1, CA1, and CF1 of whatever the current sheet is, no matter what it is named or if that name changes.

Does that make sense?

 

[HRIS]

It does. I wasn't realizing the fact that the hyperlink is to a range on the same page where the link is displaying. I checked and it appears that the named range still works fine in that case. So I set up a named range on Sheet1 and also put the hyper link on Sheet1. I created a copy of Sheet1 by right clicking the tab, selecting "Move or Copy...", selected Sheet1 from the list, checked the box next to "Create a copy," and clicked OK. On the new Sheet1(2), I clicked on the hyperlink and it took me to the equivalent range on the new spreadsheet. So, using named ranges, if it is a hyperlink to another sheet it will keep the link to the other sheet even if that sheet is renamed and if it is a link to a range on the same sheet, it will be a link to the same range on the new sheet created when using Excel's copy sheet function - which is what you are needing, correct?

 

[danielstone121]

Yes! That is exactly what I need. I will give it a try and see how it goes! I can't thank you enough!

 

[HRIS]

My pleasure! Let us know if you run into any issues!