Hyperlink target after sort?

[wal]

Excel 2003

My spreadsheet is basically a list of unique terms in the first column
with various attributes of the terms in the subsequent columns. One
column is intended as the "cross-reference" column.

Say a first-column cell contains the text "xyz". I thought I could
name that cell with a unique name (e.g., "xxyyzz"), add a phrase like
"See xyz" in another row's cross-reference cell, add a hyperlink
(Insert > Hyperlink) from "See xyz" to the target "xxyyzz", and be
good to go.

But, if you sort the sheet on another column, the stupid hyperlink
still targets the cell at the original **address** where the term
"xyz" was found, not the cell where "xyz" ends up after the sort. And
as another poster noted, the brilliant programmers acknowledge the
problem all the way through Excel 2007 but have done nothing about
it. (http://support.microsoft.com/kb/214328).

Anyway, although I get hints at a potential solution in some of the
other posts on this subject, I can't figure it out. Any help would be
appreciated. Thanks.

 

[GS]

wal formulated the question :

Excel 2003

My spreadsheet is basically a list of unique terms in the first column
with various attributes of the terms in the subsequent columns. One
column is intended as the "cross-reference" column.

Say a first-column cell contains the text "xyz". I thought I could
name that cell with a unique name (e.g., "xxyyzz"), add a phrase like
"See xyz" in another row's cross-reference cell, add a hyperlink
(Insert > Hyperlink) from "See xyz" to the target "xxyyzz", and be
good to go.

But, if you sort the sheet on another column, the stupid hyperlink
still targets the cell at the original **address** where the term
"xyz" was found, not the cell where "xyz" ends up after the sort. And
as another poster noted, the brilliant programmers acknowledge the
problem all the way through Excel 2007 but have done nothing about
it. (http://support.microsoft.com/kb/214328).

Anyway, although I get hints at a potential solution in some of the
other posts on this subject, I can't figure it out. Any help would be
appreciated. Thanks.

Sorting doesn't shift cells, it just shifts contents. It loads the
contents into an array and sorts the array, then places the contents
back. If you moved the target cell to another location, for example,
the hyperlink would point to that address.

Since your values are unique, try using the Match function to specify
the address.

 

[Zaidy036]

Excel 2003

My spreadsheet is basically a list of unique terms in the first column
with various attributes of the terms in the subsequent columns. One
column is intended as the "cross-reference" column.

Say a first-column cell contains the text "xyz". I thought I could
name that cell with a unique name (e.g., "xxyyzz"), add a phrase like
"See xyz" in another row's cross-reference cell, add a hyperlink
(Insert > Hyperlink) from "See xyz" to the target "xxyyzz", and be
good to go.

But, if you sort the sheet on another column, the stupid hyperlink
still targets the cell at the original **address** where the term
"xyz" was found, not the cell where "xyz" ends up after the sort. And
as another poster noted, the brilliant programmers acknowledge the
problem all the way through Excel 2007 but have done nothing about
it. (http://support.microsoft.com/kb/214328).

Anyway, although I get hints at a potential solution in some of the
other posts on this subject, I can't figure it out. Any help would be
appreciated. Thanks.

Assign a name to the cell you are going to

 

[GS]

BTW
My suggestion requires using the HYPERLINK function in order to use a
formula to return the address.