Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|