Hyperlinks - keeping link with data, not with cell

[jeffandnicole]

I have a spreadsheet that I have various formulas and sheets. To simplify:

Sheet 1: Columns A & B have Name and Code Number. I can add additional names and code numbers to these two columns whenever necessary.

Example:

Tom 874
John 385
David 712

Hidden in adjoining columns are formulas to automatically sort them via Code Number. Next to that are the columns showing the sorted information, sorted by number.

John 385
David 712
Tom 874

Additionally, I have a seperate sheet for Tom, John & David. Since I put Tom in first, he's Sheet 2. John is Sheet 3, and David is Sheet 4. I can use hyperlinks so when I click on John, it goes to Sheet 3; David Sheet 4; Tom Sheet 2.

Now, let's say I add Kevin 192 to the next row. The hidden columns are set up so that it'll automatically resort Kevin to the top, with John, David & Tom in the next rows down.

The issue I have with the hyperlink is that it's cell specific, not content specific, as far as I know. I would like the hyperlink to move with John's info so that it would still go to Sheet 3. Same with David and Tom. Unfortunately, after the resort, If I clicked on Kevin's name (which is now at the top of the list), it would go to John's sheet, since the hyperlink is attached to that cell.

Any ideas how to allow a hyperlink to remain with the content, versus the cell? I would prefer it to be not a macro, but I'll take a macro over nothing!

[FDibbins]

Hi and welcome to the forum

You can use a hyperlink in a formula, using =HYPERLINK(), something like...
=IFERROR(HYPERLINK("#"&"'sheet1'!Aa1","this goes to sheet1 A1"),"")
Plat around and see if that is something you could use?

[Winon]

Hello jeffandnicole,

Welcome to the Forum.

Please try the attached sample Workbook. Works fine even after sorting!

Regards

[jeffandnicole]

Ford & Winon: Thank you!!! As I had to insert the Hyperlink formula nested within an If/Then formula, I was able to use the guidence from both examples to get exactly what I needed. So far, it looks good!

(And thanks for the welcomes...I decided to forgo the "Hi" since I figured "Posts: 1" would've clued you in!)

[Winon]

Hello jeffandnicole,

You are welcome, glad we could help.

Don't forget to click on the * at the left bottom of the contibutors' Post to add to Reputation.

[FDibbins]

happy to help and thanks for the feedback

[Winon]

Hello jeffandnicole,

Thank you for the nice Rep!

Regards.