Sorting problem with referenced cells

[Steve]

Hi,

I have a situation where I have a column on one worksheet (we'll call
column 'A' on 'WS1') referenced as a column on a second worksheet
(we'll call column 'A' on 'WS2'), that causes me serious problems when
I go to sort column 'A' on 'WS1' because everything in the rows on
'WS2' that were once releative to what was in the referenced cell in
column 'A' is no longer relative. See below...

I need to know how I can keep the information that is constantly being
updated in columnts B thru D on 'WS2' from losing its relativity to
what is referenced in column 'A' when that same referenced column on
'WS1' is sorted on... Basically, I need to make sure that the
information in each row of 'WS2' stays relative to the referenced
'WS1' cell in column 'A'

Please advise,

Steve Shockley


WS1
A B C D
1
2
3
4

WS2
A B C D
1 =WS1$A$1 5 10 15
2 =WS1$A$2 20 25 30
3 =WS1$A$3 35 40 45
4 =WS1$A$4 50 55 60

 

[Jason Morin]

Bury your formula in INDIRECT.

=INDIRECT("WS1!A1")

HTH
Jason
Atlanta, GA

 

[Steve]

Jason,

Thanks for the quick response, however after I tried what you've
suggested it still doesn't fix the total problem. That is, the
adjacent information entered in the rows on 'WS2' will not stay
relative to what is in column 'A' of 'WS2' once someone has done a
sort on column 'A' of 'WS1'...any ideas?


Regards,

Steve