Hyperlink to another row broken if inserting new row between, Help

I created a sheet with hyperlinks to other cells in different rows that point
hyperlink and it works well. Problem is that if I add new rows between the
two rows, the hyperlink for the lower numbered row is off by 1 row less than
hyperlink it orginally pointed to. I use Excel 2000.

Does newer versions of Excel have the smarts to do what normal linking does?
Is there a VBA script that can relink my broken links based on a column with
a common number to each row? I dont want to have to write this if it already
exists.

Thanks in advance.
KJM

It depends on a few things...

How did you create the hyperlink?
Is that cell in the hyperlink in a worksheet in the same workbook?
If it's a different workbook, is that other workbook open?

If you used insert|hyperlink (no matter what the other answers are), I think the
easiest solution would be to name that range that your link points to
(Insert|name|define). Then use that name when you create the hyperlink.

If you used the =hyperlink() worksheet formula and you're staying within the
same workbook (or that other workbook is open), then you can use this kind of
formula (from a David McRitchie post):

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

If the link points to another workbook and that other workbook is closed, then
use a range name in the worksheet function, too.



KJM wrote:
>
> I created a sheet with hyperlinks to other cells in different rows that point
> hyperlink and it works well. Problem is that if I add new rows between the
> two rows, the hyperlink for the lower numbered row is off by 1 row less than
> hyperlink it orginally pointed to. I use Excel 2000.
>
> Does newer versions of Excel have the smarts to do what normal linking does?
> Is there a VBA script that can relink my broken links based on a column with
> a common number to each row? I dont want to have to write this if it already
> exists.
>
> Thanks in advance.
> KJM

--

Dave Peterson