Hyperlinks are hardcoded, anyway to have them move as I insert rows or columns?

[davidx]

One of the biggest weaknesses of Excel is when you are linking to another cell in Excel using a hyperlink, { e.g. Ctrl k , Place in this document, Type the cell reference } you have to hardcode the cell reference. This obviously displaces it when I insert rows. Does anybody know how to create hyperlinks to other cells in Excel which update the same way normal cells do, i.e. when you insert rows the references change. Thanks

[martindwilson]

try
=HYPERLINK("#sheet2!A"&ROW(Sheet2!A1))
if you insert a row above row 1 of sheet 2
it changes to

=HYPERLINK("#sheet2!A"&ROW(Sheet2!A2))
if you want the text from the cell on sheet 2 add the friendly name as
=HYPERLINK("#sheet2!A"&ROW(Sheet2!A1),Sheet2!A1)

[davidx]

ok thanks, but I am having some dire problems with =hyperlink which i have googled endlessly to no avail. It always tells me "cannot open the specified file" even when I am hyperlinking to other cells within the same workbook. Its completely messed up. I saw many complaints about this online but no solutions. Do you have any ideas. Its so frustrating... there's really no reason for such a simple formula to fail. Thanks

[martindwilson]

both those work fine make sure the # is in there,try saving the workbook first before entering the formulas
perhaps
=HYPERLINK("#"&"sheet2!A"&ROW(Sheet2!A2)) is clearer ,to link in same workbook the sheet name is preceded by#

[davidx]

ah right , I see you need the hash, its really odd how it doesnt work the way it should, thanks

[davidx]

ok, there's a problem, I have spaces in my sheet name, that seems to kill the row function!!!

[martindwilson]

you need single quotes
=HYPERLINK("#"&"'sheet 2'!a"&ROW('Sheet 2'!A4),'Sheet 2'!A4)

[davidx]

briliant, cant thank you enough