Creating links within an Excel sheet that will still work after row deletions or additions

[MikeWard99]

Let's say I create an index number "[1]" in cell (say) C2 .

And I create the equivalent of an endnote in (say) A99 with the text "[1] Here is the text of my endnote."

I now right-click on C2 and choose "Link" and "Place in this Document" and "Type the cell reference" and make that "A99".

I now have a working hyperlinked endnote.

I now insert a row at A50. "[1] Here is the text of my endnote." obviously moves to A100, but my link still points to A99.

What I obviously need is a relative link from C2 rather than an absolute link. Is there a way to do that?

PS One solution would would seem to be to create a named range (of one) for each link target. I can do that but that's far too complicated to ask the person I'm doing the spreadsheet for to do that. Is there no simple way to create relative links?

[#2]

@MikeWard99 

You can use a defined name:

Select cell A99.

Type a name in the name box on the left hand side of the formula bar, for example Endnote1, then press Enter.

When you insert or delete cells above A99, Excel will automatically adjust the definition of Endnote1.

Select C2. If you have already created the hyperlink, right-click the cell and select Edit Hyperlink from the context menu, otherwise select Link.

You should now see Endnote1 under Defined Names. Select it and proceed as usual.

[#3]

@Hans Vogelaar 

Thanks for the response.

That's what I meant in my "PS". I managed to get that working and it does produce robust links. But it's a bit of a paralarva for ordinary users. The option to use a relative cell reference would be far easier for less nerdy users :)