Can I sort and not lose reference to the cell?

[RedHead 1]

I am using Excel 2003. I would like to sort data in sheet 1, but I have
referenced these cells in formulas in sheet 2. Every time I sort, the
formula does not follow the cell.

I SWEAR that I have been able to sort before without messing up my formula
references. Am I crazy? (I rarely use the computer I am working on...is it
just this version of Excel or something?)

In case you don't understand what I mean, here is an example:

Step 1- I would like Sheet2 A1 to equal the same value as Sheet1 A2. The
value of Sheet1 A2 is 262.

Step 2- In Sheet2 A1, I enter "=Sheet1!A2". It shows the value as 262.

Step 3- I want to sort Sheet1. I sort them.

Step 4- The formula is Sheet2 A1 does not follow the cell with the value of
262 (for this example, lets say that value moves from cell A2 to cell A5.
Instead, it stays stuck on cell A2, which messes up my formulas on Sheet2
because they now reference the wrong value.

Isn't there a way to sort without messing up all the values??

Hope I haven't confused everyone

 

[JMB]

Does the table contain a field that could be used in a lookup formula instead
of linking the cells directly?

I'll sometimes add a field to the table called "original sort order" so that
I can always put the table back in it's original order. If nothing else,
this could be used as a key for a lookup formula on Sheet2.

 

[RedHead 1]

Thanks JMB! I know what you mean about adding an original sort order column.
I have also done this before. But, in this case, I must link directly to
the cells.

 

[JMB]

I must admit I'm having trouble understanding why the cell has to be directly
linked instead of using some sort of lookup. For me, XL has always behaved
this way when sorting data (ie - linked cells don't change). Only other
thing I can think of off the top of my head is to have the data on two tabs,
one unsorted (and linked) and the other sorted (I'm assuming for
presentation).