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.
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.
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: HyperlinkNames.xlsx