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

K

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.
 
D

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)
 
Top