Excel – Unbreakable Sheet Hyperlink [VIDEO]

Changing a sheet name and deleting the hyperlink cell are two processes that can break Excel sheet hyperlinks. The video is at the bottom of the page.

Using a range name can handle the sheet renaming issue. Deleting the column or row of the named range can still break the hyperlink.

Unbreakable?

This technique is unbreakable to the extent that you must delete the whole sheet to break it. Obviously if the sheet is deleted then the hyperlink is no longer valid or needed.

Sequence

This technique requires the hyperlink to be created in the normal sequence. Step 3 must be done after the hyperlink is created.

1 Create a normal range name referenced to cell A1 (or any other cell) in the destination sheet. Select cell A1 and click the Name Box to the left of the Formula Bar and type the name and press Enter – see image below.

Create Range Name in Excel

If it worked the name will be centred in the Name Box. See note at the bottom of post on using a pre-fix for hyperlink name.

2 On another sheet use Ctrl + k to create the hyperlink. Click the Place in this Document and use the named range (Defined Names section) see image below.

Create Excel Hyperlink

3 On the Formulas Ribbon tab open the Name Manager. Select the range name. See image below

Name Manager

 

Delete the reference in the Refers to: box and in its place type

=INDEX(

Then click the whole sheet button (top left corner of grid) – see image below.

Name Manager INDEX function

Finish off the formula (the sheet name will obviously be different) so that it is now

=INDEX(Contents!$1:$1048576,1,1)

This is a formula-based range name.

Click the tick (left of formula) to accept the changes and click Close. The hyperlink is ready to use and test. Deleting row 1 or column A leaves the hyperlink unaffected.

Completed Formula

The ,1,1 at the end of the formula refers to the first row and the first column or cell A1. Using ,2,3 would refer to cell C2.

The reason you must amend the name after the hyperlink is created is that formula-based names are not listed in the Defined Names list.

The INDEX function is powerful and allows you to refer to a specific cell within a range.

Hyperlink Name Recommendation

I used a pre-fix for the above range name. Using pre-fixes for hyperlink names means they are less likely to get confused with range names used for formula. They also list together in the Name Manager.

You can see the technique demonstrated in the video below.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply to ipad customizing Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 thoughts on “Excel – Unbreakable Sheet Hyperlink [VIDEO]

  1. Absolutely Brilliant !!
    Have run into this problem more times than I care to count. Finally a solution.

    Thank you – Thank you