How make hyperlink refer to cell content rather than cell address.

[Guest]

An Excel spreadsheet with names in Column A.
"Bush, George" is in cell A75.
"Dubya" is in cell A125.
I want a hyperlink from "Dubya" to "Bush, George."
If I add or delete rows above "Bush, George," I want the hyperlink to follow
the name, not just keep pointing to call A75.

Help? (Thanks!)

 

[Dave Peterson]

Either name that range (a75) (insert|name|define) and point at that name or use
a worksheet formula:

=HYPERLINK("#"&CELL("address",A75),"Click me")

 

[Guest]

Thanks for your fast and helpful response, Dave. The 'insert name' method is
what I've been using, but it's cumbersome... The HYPERLINK function seems
like it should work, but I must be doing something wrong. I start with...

A1 = Adams
A2 = Bush
A3 = Carter

I insert a row below 'Bush' and create a hyperlink using the hyperlink
function you suggested and I get, in A3, a hyperlink labeled 'Dubya' and
pointing to Cell A2. Great!

A1 = Adams
A2 = Bush
A3 = Dubya (hyperlink pointing to Bush)
A4 = Carter

But then I sort my list so Dubya goes after Carter

A1 = Adams
A2 = Bush
A3 = Carter
A4 = Dubya (hyperlink pointing to Carter)

and now the hyperlink in A4 points to Cell A3 -- Carter. How can I get it
to stay with 'Dubya'?

THANKS!

 

[Dave Peterson]

Sorting screws up lots of stuff.

Can you accomplish what you want using Data|Filter|autofilter?

You could name the cell, but that'll get messed up after the sort, too.

But you could put the name in an adjacent cell, sort and then reapply the name.