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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!)
 
Either name that range (a75) (insert|name|define) and point at that name or use
a worksheet formula:

=HYPERLINK("#"&CELL("address",A75),"Click me")
 
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!
 
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.
 
Back
Top