Worksheet Reference Doesn't Update After Sort

[brad]

Hi,

I have the following problem:-

A spreadsheet with two different worksheets. I have a list of numbers
in column A of one worksheet [2000 to 4153], with a list of names in
column B [A Adams to Z Zeta]. I have that same list of numbers
replicated in another worksheet, via references [eg. in worksheet 2,
cell A1 I have =Sheet!A1, which evaluates to 2000; in cell A2 I have
=Sheet!A2, which evaluates to 2001; etc.]

Now, if I insert a blank row between, say, 2000 and 2001 on the first
worksheet, the reference in the second worksheet automatically updates
[eg. =Sheet!A2 moves down to =Sheet!A3]. This is what I want to
happen.

However, if rather than inserting/deleting, I re-sort the table in the
first worksheet, the cell references in the second table don't
change. So, for example, if after re-sorting 2000 has been moved to
position A20 and position A1 is now occupied by 3250, my references on
worksheet 2 have not moved to =Sheet!A20, but stays at =Sheet!A1.

So, does anyone know how I get my cell references on worksheet 2 to
follow the numbers, when I re-sort worksheet 1?

Thanks

 

[Gord Dibben]

In Sheet2 A1 enter

=INDIRECT("Sheet1!A" & ROW())

Copy down.


Gord Dibben MS Excel MVP