Hyperlinks static but formulas are not. Why?

[Hari]

Hi,

If I set up hyperlink from one cell to a cell in another wksheet (same
workbook), then the link doesnt get updated if I change the name of the
target wksheet.
On the contrary if i write a formula in sheet1 of cell A1 -- =Sheet2!D15 --
then when i change the name of the worksheet Sheet2 to NewSheet2, the
formula in sheet1 of cell A1 also gets updated to -- =Newssheet!D15

How is it that formulas get updated on changing name of the wksheet, but
hyperlinks dont get updated?

 

[Jim Rech]

How is it that formulas get updated on changing name of the wksheet, but
I don't know what MS's thinking was on this. As a way to avoid this problem
you might consider linking to defined names that refer to cells rather than
directly to cell addresses. Defined names will adjust to sheet name
changes, as well as to inserting rows, etc.


--
Jim Rech
Excel MVP
| Hi,
|
| If I set up hyperlink from one cell to a cell in another wksheet (same
| workbook), then the link doesnt get updated if I change the name of the
| target wksheet.
| On the contrary if i write a formula in sheet1 of cell A1 --
=Sheet2!D15 --
| then when i change the name of the worksheet Sheet2 to NewSheet2, the
| formula in sheet1 of cell A1 also gets updated to -- =Newssheet!D15
|
| How is it that formulas get updated on changing name of the wksheet, but
| hyperlinks dont get updated?
| --
| Thanks a lot,
| Hari
| India
|
|

 

[Dave Peterson]

Instead of Insert|Hyperlink, maybe you could use the =hyperlink() worksheet
function:

David McRitchie posted this and it might help you:

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