can't move a named cell without breaking a hyperlink to that cell
 

Excel Gurus:
I have some named cells in a worksheet. In another worksheet I have
hyperlinks that refer to those named cells. When I sort the worksheet with
the named cells the hyperlinks on the other sheet don't follow the named
cells they originally referred to. It seems that they just keep pointing to
the actual cell position instead. How can I fix this?

Kindest Regards,
Snakeye

Cell names do not move when you sort, so it is not surprising that the
hyperlinks to those named ranges remain the same.

The only way I can think of to do it would involve a number of
worksheet functions: HYPERLINK, MATCH and OFFSET

Supposing that the place you wanted to link to was in column 4 of a
table named MyTable, on the row in which the first cell contained the
value which is in cell A2 on the sheet containing the hyperlink.

=HYPERLINK(OFFSET(MyTable,MATCH(A2,OFFSET(MyTable, 0,0,,1),0)-1,3,1,1),
"Text you want to show")

Then, when you sort MyTable, the link will still go to cell in column 4
on the row beginning with the item you require

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks for the reply Bill.
I'm a bit confused with your functions example. Is there a web location with
more detail or examples on doing this?

Snakeye

"Bill Manville" wrote:

Cell names do not move when you sort, so it is not surprising that the
hyperlinks to those named ranges remain the same.

The only way I can think of to do it would involve a number of
worksheet functions: HYPERLINK, MATCH and OFFSET

Supposing that the place you wanted to link to was in column 4 of a
table named MyTable, on the row in which the first cell contained the
value which is in cell A2 on the sheet containing the hyperlink.

=HYPERLINK(OFFSET(MyTable,MATCH(A2,OFFSET(MyTable, 0,0,,1),0)-1,3,1,1),
"Text you want to show")

Then, when you sort MyTable, the link will still go to cell in column 4
on the row beginning with the item you require

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Snakeye wrote:
I'm a bit confused with your functions example. Is there a web location with
more detail or examples on doing this?

Not that I know of; you are the first person to have requested a solution to
this problem with regard to hyperlinks.

If you need the formula explaining:

OFFSET(MyTable,0,0,,1) is the first column of MyTable
MATCH(A2,OFFSET(MyTable,0,0,,1),0) is the index (from 1) within that column of
the row containing the value which is in cell A2.
OFFSET(MyTable,MATCH(A2,OFFSET(MyTable,0,0,,1),0)-1,3,1,1) is the cell in
column 3 of MyTable in the row which has in its first column the value which is
in cell A2. This is the destination of the hyperlink.

If you want to understand the individual Excel functions better, I suggest you
try Excel's help.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup