How do I create a smart hyperlink?

[jesseseamon]

(FYI I am running 2008 Excel for Mac, but if you have an answer for a different version of Excel, it will still be helpful)

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

• <button type="button" class="msaActionText">July 30, 2011</button>
• <button type="button" class="msaActionText">Reply with quote</button>
• <button type="button" class="msaActionText msgAbuseBtn">Report abuse</button>
  • Child exploitation or abuse
  • Harassment or threats
  • Inappropriate/Adult content
  • Nudity
  • Profanity
  • Software piracy
  • SPAM/Advertising
  • Virus/Spyware/Malware danger
  • Other Term of Use or Code of Conduct violation

<input value="Reply" type="button"> <input value="Edit" type="button">
1 Person had
this question <input value="Me Too" type="button">
jesseseamon

 

[Andrew Poulsom]

Welcome to MrExcel.

Name the target cell eg MyName. Then you can use:

=HYPERLINK("#MyName","CLICK HERE")

 

[jesseseamon]

That works beautifully! Thank you so much for your quick response!