Hyperlink is not updated after inserting rows.

[Flavio Balassiano]

Hi,
Suppose I created a hyperlink that points to cell A10.
If I insert a new row between rows 9 and 10, my "old" A10
cell now is A11.
BUT, my hyperlink still points to A10.
Is there any way to make it dinamically, it means, when I
insert a new row, all hyperlinks are automatic updated ?
..
Thanks.
Flavio

 

[Jan Karel Pieterse]

Hi,

Consider using the hyperlink function to create the links:

=HYPERLINK("#" & SUBSTITUTE(CELL("address",Sheet2!A4),LEFT
(CELL("address",Sheet2!A4),FIND("]",CELL("address",Sheet2!
A4))),""),"test")

The above links to cell A4 on sheet2. The cell with the
link shows "test". Inserting cells above to linked cell
will update the link.

Regards,

Jan Karel Pieterse
Excel TA/MVP

 

[Dave Peterson]

This shorter version seems to work ok, too:

=HYPERLINK("#"&CELL("address",'my sheet2'!A4),"test2")

(I changed the name to 'my sheet2' to show that you need apostrophe's if your
worksheet contains spaces.)

Hi,

Consider using the hyperlink function to create the links:

=HYPERLINK("#" & SUBSTITUTE(CELL("address",Sheet2!A4),LEFT
(CELL("address",Sheet2!A4),FIND("]",CELL("address",Sheet2!
A4))),""),"test")

The above links to cell A4 on sheet2. The cell with the
link shows "test". Inserting cells above to linked cell
will update the link.

Regards,

Jan Karel Pieterse
Excel TA/MVP

-----Original Message-----
Hi,
Suppose I created a hyperlink that points to cell A10.
If I insert a new row between rows 9 and 10, my "old" A10
cell now is A11.
BUT, my hyperlink still points to A10.
Is there any way to make it dinamically, it means, when I
insert a new row, all hyperlinks are automatic updated ?
..
Thanks.
Flavio
.

 

[Jan Karel Pieterse]

Hi Dave,

This shorter version seems to work ok, too:

=HYPERLINK("#"&CELL("address",'my sheet2'!A4),"test2")

Which shows I assumed it needed just the sheet & cell address.

Thanks.

Regards,

Jan Karel Pieterse
Excel TA/MVP