Lock Hyperlink to cell in different sheet

R

Regina

HI.

I want to create a link from one cell to another cell in a different sheet
but same workbook. But how can I get the link to follow when adding rows in
the linked to sheet?
Ie. Cell A5 in sheet1 has a link to cell B7 in sheet2.
If I add 2 rows to sheet2 so my previous cell B7 is now B9. I want the link
now to go to B9 in sheet2.
Is this possible, and if so how?
 
S

Stefi

Define a name for B7 and link to that name!

Regards,
Stefi

„Regina†ezt írta:
 
M

Max

Presuming the focus is on hyperlinks (not simple link formulas),
as per your subject line

For the initial hyperlink to Sheet2's B7, try:
=HYPERLINK("#"&CELL("address",INDIRECT("'Sheet2'!B"&ROW(Sheet2!B7))),"MyLink")

Tested here, works ok with the stated row insertions. Insert 2 rows in
Sheet2 above row 7, the hyperlink above will adjust & jump to B9 in Sheet2

High-five? Click YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
 
R

Regina

Hmm, I get an error inserting the formula.
Should "address" be replaced by something?
 
M

Max

Regina said:
Hmm, I get an error inserting the formula.
Should "address" be replaced by something?
Click to expand...

Earlier was overkill. Copy n paste (don't re-type) this simpler one:
=HYPERLINK("#"&CELL("address",Sheet2!B7),"MyLink")
Should work. But the OP wasn't looking for this expression.
Its better to start new threads if you have queries of your own.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik
---
 
Top