Change in another cell with respect to the value in active cell

[sivdin]

Hello Experts,

I have different status in the range for eg: A,B,C,D,E,F

Let say if the status is "A" in the range "B2", then a numeric value "1" should be added in corresponding row range "F2"

Similarly if the status is changed to "B" then numeric value should be "2"

Can anybody help me out a code for this?

Thanks in Advance.

[ConneXionLost]

Hi sivdin,

Try this:

Please Login or Register  to view this content.

Cheers,

[teylyn]

taking into account that LOOKUP returns the closest match, you'd get a 6 for any letter higher up the alphabet than "F", you may want to try VLOOKUP instead, with a test for a match first.

=IF(ISNA(MATCH(B2,{"A","B","C","D","E","F"},0)),"no match",VLOOKUP(B2,{"A",1;"B",2;"C",3;"D",4;"E",5;"F",6},2,FALSE))

[sivdin]

Experts,
Please find the sample attached.
Range "A" denotes the status and range "D" has the desired values corresponding to the status

Whenever i change the status in the the correcponding value should change.

Hope the data is clear for you..

[teylyn]

OK, If you enter a letter in G1 and want H1 to show the appropriate status, use

=IF(ISNA(MATCH(G1,$A$2:$A$8,0)),"no match",VLOOKUP(G1,$A$2:$D$8,4,FALSE))

hth

[sivdin]

Thats Cool.. Its working.
Thanks a lot for the solution.
This Forum always rocks.........