having trouble locking hyperlinks in excel
 

Hi all. I have a workbook with multiple worksheets. I have hyperlinks between
the first worksheet and others in the book, just using the cell reference (eg
E4). The problem I have is when I add a row, the links are messed up.

I tried naming the cells I'm linking to, but then the links didn't work at
all.
Any ideas?
--
Thanks! Chatnoir

having trouble locking hyperlinks in excel
 

A formula like:

=HYPERLINK("#Sheet2!I14","go")

will lock to the absolute address. So if you add rows before row 4, it will
always go to the fourth row. However:

The following uses the HYPERLINK() function to goto Sheet3 cell Z100:

=HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ")

This link will "adjust". So if you add or remove rows above Z100, the
formula will adjust!

--
Gary''s Student - gsnu200713

having trouble locking hyperlinks in excel
 

If you used insert|Hyperlink, then you should try using the named range again.
It should work ok.



Chatnoir wrote:

Hi all. I have a workbook with multiple worksheets. I have hyperlinks between
the first worksheet and others in the book, just using the cell reference (eg
E4). The problem I have is when I add a row, the links are messed up.

I tried naming the cells I'm linking to, but then the links didn't work at
all.
Any ideas?
--
Thanks! Chatnoir

--

Dave Peterson

having trouble locking hyperlinks in excel
 

I understand that #Sheet2 is the worksheet name, but in teh next example what
do #"&CELL mean?

(I'll try the first one now.. since I kinda get it. :-}
--
Thanks! Chatnoir


"Gary''s Student" wrote:

A formula like:

=HYPERLINK("#Sheet2!I14","go")

will lock to the absolute address. So if you add rows before row 4, it will
always go to the fourth row. However:

The following uses the HYPERLINK() function to goto Sheet3 cell Z100:

=HYPERLINK("#"&CELL("address",Sheet3!Z100),"target ")

This link will "adjust". So if you add or remove rows above Z100, the
formula will adjust!

--
Gary''s Student - gsnu200713

having trouble locking hyperlinks in excel
 

Gary's 2nd method worked for me. Thanks! I'll try Dave's too - it looks a
little easier.Appreciate the great help.
--
Thanks! Chatnoir


"Dave Peterson" wrote:

If you used insert|Hyperlink, then you should try using the named range again.
It should work ok.



Chatnoir wrote:

Hi all. I have a workbook with multiple worksheets. I have hyperlinks between
the first worksheet and others in the book, just using the cell reference (eg
E4). The problem I have is when I add a row, the links are messed up.

I tried naming the cells I'm linking to, but then the links didn't work at
all.
Any ideas?
--
Thanks! Chatnoir

--

Dave Peterson

having trouble locking hyperlinks in excel
 

Actually, if I were doing this with lots and lots of hyperlinks, I'd use Gary's
second suggestion, too.

I think the =hyperlink() worksheet function is better behaved.

Chatnoir wrote:

Gary's 2nd method worked for me. Thanks! I'll try Dave's too - it looks a
little easier.Appreciate the great help.
--
Thanks! Chatnoir

"Dave Peterson" wrote:

If you used insert|Hyperlink, then you should try using the named range again.
It should work ok.



Chatnoir wrote:

Hi all. I have a workbook with multiple worksheets. I have hyperlinks between
the first worksheet and others in the book, just using the cell reference (eg
E4). The problem I have is when I add a row, the links are messed up.

I tried naming the cells I'm linking to, but then the links didn't work at
all.
Any ideas?
--
Thanks! Chatnoir

--

Dave Peterson


--

Dave Peterson