Values remain absolute when Sheet2 re-sorted

[sabw]

When using formula =Sheet2!C3 in cell C3 of Sheet1, cell reference values stay absolute if Sheet2 is re-sorted producing incorrect values.

Is there a formula correction to remedy this?

Sheet1

A B C

agf aim

agf gbd =Sheet2!C3

agf shn

agf lku

agf ytr

agf plm

agf ijn


Sheet2

A B C

agf aim 10

agf gbd 20

agf shn 30

agf lku 40

agf ytr 50

agf plm 60

agf ijn 70

 

[NARAYANK991]

Hi ,


Excel is doing exactly what it has been told to do !


When you use a formula such as =Sheet2!C3 , what you are telling Excel is to put the value which is found in cell C3 on the Sheet2 tab into the cell where the formula is entered.


Now , if by sorting Sheet2 so that the combination of agf , abd and 20 ( since you want these three items to go together ) goes to some other row , say row 7 , the formula is not going to change ; it will continue to retrieve the value found in cell C3 on the Sheet2 tab , and put it into the cell where the formula is present.


If you wish that wherever the combination is to be found on Sheet2 , the value should be entered in the corresponding cell on Sheet1 , then the formula has to be :


=INDEX(Sheet2!$C$2:$C$8,MATCH(A3&B3,Sheet2!$A$2:$A$8&Sheet2!$B$2:$B$8,0))


Enter this as an array formula , using CTRL SHIFT ENTER.


Narayan

 

[sabw]

Thanks Narayan