How do I keep a hyperlink valid if the link to cell is moved?

[kendeboer01]

If I use a hyperlink from one cell to another in the same worksheet and then
the cell to which the link refers moves within the worksheet, the link is no
longer linking to the correct cell. How do I prevent this/ Thank you.

 

[Dave Peterson]

If you're using the insert|Hyperlink type of hyperlink, then I'd replace them
with the =hyperlink() worksheet function.

You could either name the linked cell (insert|Name|define) and point to that
named cell:

=hyperlink("#TestName","Click Me")
(The name is TestName. The # is the hyperlink subaddress character.)

Or use a formula like one of these that David McRitchie posted:

=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)