Hyperlink probelm

G

Glenn Richardson

I have a spreadsheet with a couple of worksheets, in one of the
worksheets I have a hyperlink to a specific cell in the other
worksheet.

e.g. Cell A1 in worksheet 1 has a link to cell A2 in worksheet 2 (with
the text 'Phone' in it). If I was to insert a row above row 2 in
worksheet 2, I still want the link in worksheet 1 to take me to the
cell with the text 'Phone' in it (Which is now A3 after I have inserted
the row).

I realise I can go and change the link manually, but the example above
is a simplfied version of my document and inserting a row moves about
30 hyperlinks!!!

Any help would be greatly appreciated.

Cheers
Glenn
 
D

Dave Peterson

Instead of using 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)


If you really want to stick with the Insert|hyperlink version, you could give
your target cell a nice range name (Insert|Name|define), then use that when you
create the hyperlink.
 
D

David McRitchie

Hi Glenn,
You want to use object hyperlinks instead of Hyperlink Worksheet Function
meaning you want to use Ctrl+K to make your hyperlink.

If you want to use Worksheet Function Hyperlinks you will have to take
the address out of quotes where it is locked in.
=HYPERLINK("#"&CELL("address",'sheet one'!C5),'sheet one'!C5)

More information on hyperlinks on my sheets.htm and buildtoc.htm pages.
 
D

David McRitchie

I think I just lost 3 hours of my life somehow, like from when you
answered to when I answered.
 
G

Glenn Richardson

I used the "Insert|Name|Define" way of doing it and it's worked a
treat.

Many thanks for your help.

Glenn
 
D

David McRitchie

Hi Glenn,
You're welcome, when you said you only had 30 hyperlinks, I
knew the object type hyperlinks would not be a problem for you.

Thanks for feedback because I couldn't be completely sure
that you had been using the worksheet function; otherwise.

Sometimes you may get the link within your object hyperlink
unmatched to what you see, so sometimes it works best
to check the link when changing the display.

The problems with object hyperlinks and memory seem to be less and
less as time goes on. They like lots of shapes used to cause
a lot more problems with Excel memory space which is not
the same as your RAM memory augmented by virtual memory.
 
Top