Excel Help Needed

[fyodor]

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.

[ICMoney]

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.

[fyodor]

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.

[BeaucoupFish]

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).

[fyodor]

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.

[kerowo]

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?

[fyodor]

kerowo - Thanks for the info. I see where that faking it thing pretty much does the trick for me.

My problem stems from the fact that I made a huge spreadsheet that would have been more apropriate for a database. Unfortunately I know nothing about database programs at all. It was easy to enter all my info in a spreadsheet though. It was only after I had entered over 1200 cells of info that I thought of hpyerlinking some of it for ease of search.

I have 3 columns of data: Name, Year, Type
Some of the entries are related to one other entry. When I come across one of these I just want to be able to click on it and go to the other entry to see all the info on it.
I sometimes want them sorted by Name, sometime by Year, sometimes by type. So sometimes the entries are next to each other but depending on how I sort sometimes they can be 200 entries away. A hyperlink between the 2 related sets seemed like the quickest way to find the corresponding entry.
Problem is after I did my hyperlinks and resorted by a different criteria the hyperlinks were pointing to wrong locations. Also I am still adding data.

Very few of the entries need to be linked like this so a seperate table a few columns over might be the way to go. Or I could always learn to use Access. [img]/images/graemlins/frown.gif[/img]

[CrazyEyez]

You may want to try out the AutoFilter. It has nothing to do with hyperlinking, but may help you view certain records together.

[BeaucoupFish]

[ QUOTE ]
Why are you using hyperlinks instead of just grabbing the value in the cell?

[/ QUOTE ]

Sorry my suggestion doesn't work...but what is it you are attempting to do? As above, why use a hyperlink? Sounds like you want to be taken to a specific cell. There are other ways to do this with, say use a vlookup and a little VBA (nothing too complicated) linked to a command button to take you somewhere.

Details?!