How do I make a smart hyperlink?

Question

I have set up a hyperlink that takes me from a cell in sheet1 to cell A1 in sheet2 of the same workbook:

=HYPERLINK("[jesse.xls]Sheet2!A1","CLICK HERE")

This works beautifully, except for one problem.  I recently needed to edit sheet2 by adding a new row at the top.  As a result, my information in cell A1 moved down to cell A2. However, when I click on the link, it still takes me to cell A1, now blank.

Is there any way I can set up a hyperlink that is smart enough to always take me to the same data, even if that data moves to different cells as a result of row/column additions?

-Jesse

Answer 1

You could try using a named range in the hyperlink.

Answer 2

This requires Macros, else you cannot accomplish what you want.

Copy/move the Data from the A2 to A1 and then check how it works.