hyperlink to a cell: adjusting for changes?

[George]

(Excel 2002)

I recently discovered that Excel can hyperlink to another cell in the
same workbook, which looks to be very useful. In this re, ...

Using (row,col) designation, the link's 'reference' (using the word
loosly) appears to be absolute: If I set it to 'A31', and then
insert/delete row 29, the link will still be to 'A31' - as if I'd spec'd
$A$31, in most other contexts.

I know I can get around this by referencing a 'named' target cell. This
is OK, ... but, a litte more effort.

So, just to be sure I'm not missing something, is there a way to specify
a (row,col)-type hyperlink, so that it gets adjusted when insert/delete
changes move the target cell?

Thanks,
George

 

[Luke M]

Try this:

=HYPERLINK("[My Workbook.xls]'My Sheet'!"&ADDRESS(ROW('My
Sheet'!A31),COLUMN('My Sheet'!A31)),"Named Link")

Now as the cell A31 on My Sheet moves around, the hyperlink will adjust
accordingly.

 

[richard]

Presumably you mean in another workbook.
If this is the case then have both workbooks open when making the changes
and excel will do all the work for you.

 

[Dave Peterson]

If you're using the Insert|hyperlink version of hyperlinks, then you'll want to
name that target. These hyperlinks don't adjust when you delete/insert rows or
columns.

If you're using the =hyperlink() worksheet function, you could make sure that
you open both (all???) files before you make changes. (Assuming that your
formulas are straightforward.)

But if you don't want to open that workbook with those =hyperlink() formulas,
then you'll want to name the targets.

 

[Dave Peterson]

But if you don't want to open that workbook with those =hyperlink() formulas,
then you'll want to name the targets.

Should have been:

But if you don't want to open those target workbooks that your =hyperlink()
formulas point to, then you'll want to name the targets.