Help: Excel hyperlinks changing...

[StuB]

All,

I've a Excel spreadsheet, with about 10-12 worksheets on it. One of
these worksheets is a 'revision history' sheet, so it tracks all
changes to other worksheets.

I use hyperlinks to link directly to the change that has been made.

However, for some reason, these links seem to change of their own
accord. Suddenly, the link will be to a different worksheet or a
different cell reference. This has not been changed by anyone, as only
I have access to the document.

Any ideas or suggestions?

If it helps, when I add a new hyperlink in. I insert a new row in my
revision history document and add the hyperlink in. I wasn't sure if
it was anything to do with inserting the row?!?!

Thanks

 

[Dave Peterson]

I've never seen hyperlinks change when they point to worksheets in the same
workbook. But I have seen hyperlinks (inserted via Insert|Hyperlink) point to
old locations when rows/columns have been inserted/deleted (and even more
trouble if the sheet has been renamed).

You could give each location a name (insert|Name), then use insert|hyperlink to
point at that named range. Then since the name follows the cell, it won't
matter what you insert/delete.

But I really like the =hyperlink() function. Since it's a worksheet function,
it's smart enough to react to those kinds of insertions/deletions/renames--well
if you write it nicely.

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)

 

[StuB]

Cheers Dave, I think you've picked it out.

I do a lot, and I mean a massive amount, of inserting/deleting rows i
both the Change History worksheet and all the other worksheets.
thought it was something to do with that as you could sort of match u
how the hyperlinks were out of order.

I may look at using the function you list next time.

Thanks agai