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

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!)

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

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

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



Omunene wrote:

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

How make hyperlink refer to cell content rather than cell addr
 

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" wrote:

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

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



Omunene wrote:

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

How make hyperlink refer to cell content rather than cell addr
 

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.

Omunene wrote:

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" wrote:

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

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



Omunene wrote:

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


--

Dave Peterson