hyperlink w/in excel loses reference

[dueker1]

hyperlink w/in excel loses reference
this is the situation
person cell id hyperlink
Adams b20 b20
Jones b50 b50
Zilch b99 b99
If I create a hyperlink at Zilch to Jones the hyperlink = b50
then if I add a person above Jones (b50), the Jones info moves down and
the hyperlink from Zilch to Jones is no longer valid @ b50.
what do I enter to make the hyperlink remain accurate, regardless of
where I move Jones??

 

[Fishboy]

hyperlink w/in excel loses reference
this is the situation
person cell id hyperlink
Adams b20 b20
Jones b50 b50
Zilch b99 b99
If I create a hyperlink at Zilch to Jones the hyperlink = b50
then if I add a person above Jones (b50), the Jones info moves down and
the hyperlink from Zilch to Jones is no longer valid @ b50.
what do I enter to make the hyperlink remain accurate, regardless of
where I move Jones??

Hi dueker1, welcome to the boards.

One way of doing this is to used named ranges. Select cell B50 (which should be Jones), then go to the "Name Box" to the left of the formula bar. Type in Jones and press enter. That cell is now actually called Jones.

When you want to enter a hyperlink in a cell that points to this, right-click where the hyperlink will be going, click Hyperlink, then "Place in This Document", and you should see Jones is a valid option to pick.

Once the hyperlink is associated with the named range as it's destination, you can add or delete as many rows above it as you like, the hyperlink still points to the cell called Jones.

This can obviously be replicated for each name in your list.

Bear in mind that if you have full names in the cells (i.e Bob Jones, with a space) the named range cannot contain spaces. You could name the range Bob_Jones however and the system should still work.

 

[dueker1]

your solution works great. Thank you, Jim

where do I enter that your solution worked?

 

[Fishboy]

There is no "Solved" flag on this forum it seems. Just click the like button in the bottom right hand corner of the post containing the solution.

Anyway, happy to help and glad you got it working