Absolute Cell Reference in a Hyperlink within the the same workbook

**SMOE2013** · 07-12-2013, 02:04 PM

All:

I have a workbook with many tabs and within those tabs there are many hyperlinks to other areas within the same workbook. When I add or delete lines or rows, the corresponding hyperlinks do not stay with the changed cell. Is there any way to make the cell reference in a hyperlink an absolute similar to how you do with a formula?

Any help is greatly appreciated.

Thank you!

**SteelReyn** · 07-13-2013, 12:30 PM

Hi SMOE2013,

I think I know what you're asking about, but I'll give an example to see:

Say you have Sheet1 and Sheet2. On Sheet2, you define a name for cell A2 called Sheet2_A2, where the Refers to: =Sheet2!$A$2. On Sheet1, you create a hyperlink to the reference Sheet2_A2, which takes you to the expected cell.

However, if you delete row 1 of Sheet2, your hyperlink on Sheet1 will now take you to Sheet2!A1, not A2. If that is what you are trying to resolve and you want your hyperlink to still point to Sheet2!$A$2, use the Indirect function in your named reference, in this case:

=INDIRECT("Sheet2!$A$2")

This will always point to Sheet2!A2, no matter how many rows you add or delete.

If that is not the problem, can you post an example workbook so we can see your setup?

**martindwilson** · 07-13-2013, 12:37 PM

which hyperlink are you using?
if you use the hyperlink function hyperlink()
=HYPERLINK("#sheet2!a2","test") will always go to sheet2 a2 no matter what you delete