Can you reference a cell that is sorted in excel 2002?

[Guest]

I want to create a reference to a cell in one worksheet in Excel 2002 that
references the same information even if I sort the referenced cell, add
columns or rows to the referenced worksheet, or filter the referenced
worksheet.

 

[Bernie Deitrick]

Dave,

Sounds like a job for VLOOKUP, or MATCH and INDEX, or..... a better description of the problem
would help.

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie
Thank you. Let me see if I can explain the problem more clearly. In
worksheet A, I have a number in a cell that I simply want to reference in
worksheet B. However, if I sort worksheet A or possibly insert a new row or
a new column, the cell I was referencing is in a new location and the number
I wanted to reference from worksheet A is no longer referenced in worksheet
B. In other words, if in worksheet A, I enter the number "25" in cell A1 and
then want to reference the number "25 that is in cell A1 in worksheet B in
cell B2, it seems to work fine until I sort worksheet A (or add rows or
columns) such that the number "25" that was in cell A1 in worksheet A is no
longer in the cell a1 position. so my reference in worksheet 2 still points
to A1 but not the number "25" that I actually want to reference. I hope that
is somewhat more clear. Thanks.

 

[Guest]

Dave, Bernie was right when he suggested VLOOKUP or one of the other functions.

Try this.

On Sheet1 cell A2 put your reference value, 25. Next to it in cell A1 put
an identifier that will be unique within column A, such as MyRefVal.

On Sheet2 where you want the 25 to appear, use the formula
=VLOOKUP("MyRefVal",Sheet1!A:B,2,FALSE).

Just make sure that when you sort Sheet1 that colums A and B sort together.