|
#1
|
|||
|
|||
Excel Help Needed
Having a problem with hyperlinks in Excel. I want to hyperlink one cell in a spreadsheet to another cell. If I resort the data in the spreadsheet and the first cell moves somewhere else, I would like the hyperlink to find the new location. How do I do this (if it is possible)?
ie I have some data in cell B1 and when I click a hyper link at Cell C3 it finds the data at B1. I re-sort my spreadsheet and the data in B1 has now moved to B5 and the link at C3 is now at C1. When I now click the hyperlink at C1 I want it to go to B5. |
#2
|
|||
|
|||
Re: Excel Help Needed
If you are in c5 and type =a1 that is a relative link. If a1 moves to a2 c5 will now be updated to =a2.
The only way this would not be true is if you use an absolute reference. For example, in c5 you type =$a$1. If a1 moves c5 will still point to a1. |
#3
|
|||
|
|||
Re: Excel Help Needed
Sorry. I am aware of that. I wasn't clear in my explanation. I don't merely want to display the contents of the linked to cell, I want to actually jump to that cell.
|
#4
|
|||
|
|||
Re: Excel Help Needed
Depending upon how many cells you are listing, define a name for each cell first (Insert | Name | Define, or type in the new name into the cell address box, as a shortcut). Have the hyperlink link to the name instead of the address.
The link will be retained during any sorting, cut/pasting or otherwise moving of the cell(s). |
#5
|
|||
|
|||
Re: Excel Help Needed
Already tried that BeaucoupFish and it didn't work. It seems if I name a cell the name stays at that location.
ie: name A1 - "CellOne" Put some data in that cell like the number "2" Enter the number "1" in cell A2 Resort the column in Ascending order "1" is now in A1 and "2" is now in A2 A1 is still named "CellOne" So names stay with the cell not with the data. I played with that Insert/Name/Define thing till I was ready to bash my keyboard to death. Tried removing the '$' signs in the "Refers to:" box but to no avail. |
#6
|
|||
|
|||
Re: Excel Help Needed
You can't do what you want directly but can fake it with a look up table. In a part of the spreadsheet that isn't going to be sorted put =a1 or whatever cell you want to hyperlink to. Then hyperlink to the cell in the table. You won't be linking to the actual cell after you sort, but will be hyperlinking to a cell that contains the value of that cell.
Why are you using hyperlinks instead of just grabbing the value in the cell? |
|
|