recalculate hyperlink location

G

Goforth

I have a spreadsheet that changes often. After inserting or deleting a line
the location of my hyperlinks change. Is there any way to automatically
recalculate the location of the hyperlinks instead of changing then all
manually?
Thanks
 
G

Gary''s Student

If you are setting a hyperlink to a place in your worksheet, use a Named
Range rather than an absolute address:

Instead of:
=HYPERLINK("#Sheet3!$A$1")
use something like:
=HYPERLINK("#"&CELL("address",alpha),alpha)

where alpha has been set with:
Insert > names > Define...

The trick is that the Name adjusts as rows/columns are inserted/deleted.
 
D

Dave Peterson

Naming the target of the hyperlink makes those Insert|Hyperlink type hyperlinks
work much better.

But if the OP is using the worksheet function, he or she doesn't need to name
the range.

This kind of formula will adjust, too:

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

Goforth

Gary''s Student said:
If you are setting a hyperlink to a place in your worksheet, use a Named
Range rather than an absolute address:

Instead of:
=HYPERLINK("#Sheet3!$A$1")
use something like:
=HYPERLINK("#"&CELL("address",alpha),alpha)

where alpha has been set with:
Insert > names > Define...

The trick is that the Name adjusts as rows/columns are inserted/deleted.
Click to expand...

I can't thank you enough! You've save me a lot of time.
Glenn
 
G

Goforth

Dave Peterson said:
Naming the target of the hyperlink makes those Insert|Hyperlink type hyperlinks
work much better.

But if the OP is using the worksheet function, he or she doesn't need to name
the range.

This kind of formula will adjust, too:

=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)
Click to expand...
Thanks to both of you. This is exactly what I was looking for!
Glenn
 
Top