Hyperlinks Don't Sort

[Dan Raab]

When sorting, hyperlinks refer back to the original absolute cel
position, not to there the sorted data ends up, i.e. the hyperlin
points to a static location, not to the data it was originally pointe
to. Need to have hyperlinks follow the data around when sorting.

I have tried hyperlinking to range names, that fix allows row an
column inserts and deletions, but does not track when sorting.

Have also tried the =HYPERLINK("#"&CELL("address",C5),C5) fix. Sam
results as range names, allows row and column inserts and deletions bu
does not track when sorting.

Thanks,

Da

 

[David McRitchie]

Are you trying to sort the displayed content or the hyperlink itself.
Are you selecting all cells on the worksheet before invoking sort.
http://www.mvps.org/dmcritchie/excel/sorting.htm
In Excel 2003 Ctrl+A does NOT select all cells but they did not manage to
mess up a more obscure one Ctrl+Shift+SpaceBar which like
Ctrl+A (double use in Excel 2003) does not change the active cell.

If you type
www.abc.com into a cell it generates object type hyperlink to http://www.abc.com
if then type in the following into the cell
=HYPERLINK("http://www.nbc.com","nbc")
you still have the underlying link to abc.com and you will go to abc.com when
you click on the link. You can check the active cell with Ctrl+K

Give me an example of 3 cells with hyperlink, how you create them
how you sort them. How you know it failed.

 

[Dan Raab]

Thanks for the help!

In cell C2 enter some text, "Target".
In Cell C3 enter some text, "Link to Target".
Right click on C3, hyperlink, enter the cell reference C2.
Hyperlink works correctly.
Place cursor in row 1 and insert a row.
Hyperlink takes you to C2, not to the target.

There are two fixes to this problem. use th
=HYPERLINK("#"&CELL("address",C5),C5) fix, which I found on line. Or
you can also name the cell C2 with a range name and hyperlink to th
defined name.

OK, so now we can insert and delete rows and columns, but we stil
cannot sort.


New spreadsheet
Cell C2 has text "Target"
Cell C2 has a range name, say "a"
Cell C3 has text "Link to Target" and is hyperlinked to the define
name, "a"
Cell D2 has number 7
Cell D3 has number 3
Cell D4 has number 1
Cell D5 has number 4
Select the entire rows for rows 2,3,4,5
Data, Sort
No Row Header,
Column D, Ascending
The hyperlink now points to Cell C2, not to the Target.

I need some way to tie the hyperlinks to the actual location of th
target after a sort, whereever that location may be.

Please advise,

Thanks again!

Da

 

[Dan Raab]

Still looking for an answer. Any ideas?

Thanks!

 

[David McRitchie]

Hi Dan,
Okay you can't do that. Is there another way that you can
identify the cell you are trying to link to, like perhaps with
VLOOKUP. If not its sound like something for a database
application like identify parents, children, siblings.

Or perhaps make a copy of the sheet saving as values to
another sheet and then sort that sheet.