Link to Sheets using Defined Names
Hyperlinks are a useful way to expedite navigation. If you have ever created a hyperlink to a worksheet, you may have noticed that your hyperlink can break if you change the worksheet name. This post demonstrates how to use defined names to avoid this type of error.
Objective
Let’s say we want to make it easy for users to navigate to various worksheets within the workbook. We create a little list of the key worksheets with hyperlinks like this:
These hyperlinks were created by using the Insert > Link > Insert Link command. In the resulting dialog, we select Place in This Document, and then pick the sheet name from the list, such as MyData:
Once the hyperlinks are set up, we can click them to easily navigate to the selected sheet. Life is good!
… Time Passes …
Later, we decide to rename one of those worksheets.
To our surprise, when we click on the related hyperlink we receive an unexpected error saying the Reference isn’t valid:
We receive this error because Excel doesn’t automatically update hyperlinks when we change a sheet name. Excel DOES update formulas when we update a sheet name. However, it doesn’t monitor hyperlinks the same way … thus the error.
No worries! We’ll just use a different hyperlink option when setting up our links … specifically, we’ll use Defined Names.
Steps
Let’s update our hyperlinks so they don’t break if we change the sheet name. We’ll walk through the following steps together.
- Create a Defined Name
- Link to the Defined Name
- Test it out
Create a Defined Name
We want to create a Defined Name on each of the destination sheets. To do so, we just head to the first destination sheet and select cell A1 (or other cell if desired). Then, we enter our desired Defined Name (such as DataSheet) into the Name Box and press Enter:
Note: the Name Box is just to the left of the formula bar.
You can enter just about any descriptive name you want … be sure to avoid spaces and funky characters.
Do this for each worksheet that represents a hyperlink destination.
Link to the Defined Name
Once we have the Defined Names all set up, it is time to create our hyperlinks. We use the same Insert > Link > Insert Link command that we used last time. This time however, we see that our new Defined Names appear under the sheet list:
We select the corresponding Defined Name and click OK.
We repeat for each of the links we which to create.
Test it out
With our updated hyperlinks in place, it is time to test them out.
We click on a link, and it works as expected!
… Time Passes …
We change one of the sheet names. This time when we click the corresponding hyperlink … it continues to work!
This technique works because Defined Names are essentially formulas. And, Excel carefully monitors formulas and updates them as needed. So, if we rename a worksheet, Excel automatically updates the Defined Name formula accordingly. This means that the hyperlink that references the Defined Name will continue to work as expected.
Be aware that if you were to delete the cell, row, or column referenced by the Defined Name then you would need to update the name accordingly. In other words, if your Defined Name linked to cell A1 on a particular sheet, and then you deleted cell A1, row 1, or column A … the reference would no longer be valid. So, you would need to update the Defined Name … which you can do by using the Name Manager.
If you have any other suggestions or hyperlink tricks, please share by posting a comment below.
Sample File
- Sample file: HyperlinkNames.xlsx
Excel is not what it used to be.
You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.
Want to learn Excel?
Our training programs start at $29 and will help you learn Excel quickly.
Great tip, Jeff!
Thanks Jon 🙂
Hi Jeff
That’s really useful, thank you
Just to add to this – Don’t forget the link to go back to the original page!
Cheers
Keith
Ah … nice touch, thanks 🙂
You can also use a formulaic Defined Name as opposed to a Named Range. The distinction is that instead of selcting a cell and giving it a name, you can access the Name Manager and create a new name using a formula to establish the desired reference. Perhaps something like…
=INDEX(‘Test Sheet’!$1:$1048576,1,1)
With this approach, you can never delete a cell, row, or column that will break the reference. You can even use this with the HYPERLINK function — my preferred method — as opposed to embedded links. Say your Defined Name was MyData. Then you would use…
=HYPERLINK(“#MyData”,”Pretty Text”)
Great … thanks!
looks like we crossed over!
much the same, but yours is a little more elegant
…and better explained
(but really I blame Jeff for taking so long to moderate his comments) 😉
To avoid losing the hyperlink when you delete rows etc, redefine the name (after you’ve linked to it) to be an OFFSET formula to a cell on the destination sheet: =OFFSET(‘Sheetname’!1:1048576,,,1,1)
This will always link to that sheet’s cell A1 even if you delete cells, rows or columns, or rename the sheet
But you have to link to it as a named cell first and then redefine the name it as a formula
Thanks!
Hi Jeff,
Is there a way to change the hyperlinks in the entire column at once ? Or to make the hyperlink pick the right column when I add a column to the hyperlink referring sheet? My issue is when I add a column to my main sheet the hyperlink in another sheet in the same file picking the wrong column.
I need to link it to a specific cell reference on a tab eg on the “Index” tab, link to say D7 on “My Data” tab. Thought this would solve my problem as i need to save the files Revision 1, revsion 2 etc and i lose the links. Is there a way to solve this problem?
Thanks for all the tips.
The # in my previous example (repeated here) with the HYPERLINK function is a cheat reference to “this file” as a means of preserving links even when the file name changes.
=HYPERLINK(“#MyData”,”Pretty Text”)
I have been search for the answer of how to link to a specific excel page (from power-point) for about an hour. Your instruction to use the defined name field has solved my issue with getting “Reference isn’t valid” errors.
Thank you for creating this post.
Hi Jeff,
Is there a way to change the hyperlinks in the entire column at once ? Or to make the hyperlink pick the right column when I add a column to the hyperlink referring sheet? My issue is when I add a column to my main sheet the hyperlink in another sheet in the same file picking the wrong column.