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.
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.
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.
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.
3 On the Formulas Ribbon tab open the Name Manager. Select the range name. See image below
Delete the reference in the Refers to: box and in its place type
Then click the whole sheet button (top left corner of grid) – see image below.
Finish off the formula (the sheet name will obviously be different) so that it is now
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.
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.