return a value from another cell

[Guest]

hi all,

i have a problem returning a value from another cell.

in sheet 1, i have:
column A 'ID' : 1, 2, 3, 4, 5
column B 'S' : a, b, c, d, e
column C 'C' : A, B, C, D, E
this is the basis for the other page, it should be possible to return these
different values into the other page.

in sheet 2 i have:
column A : 1, 3, 1, 5, 4, ... (the same ID values as in sheet 1)
column B : S or C ("S" refers to column B in sheet 1, "C" refers to column C
in sheet 1), i.e. ID can be either "S" or "C".

column C : here's my problem.

i want to add a formula that returns the value from either column B or C
from sheet 1 that corresponds with the value in column A of sheet 2.

example:
SHEET 2:
A2 = 3
B2 = S
than the value in C2 should be 'c'.

on the otherhand, if:
SHEET 2:
A2 = 3
B2 = C
than the value in C2 should be 'C'.

i managed to solve the problem via vlookup, but the thing is that if
somebody inserts a column in sheet 1, the formula doesn't work anymore.
is there a 'safer' possibility that copes with this (e.g. by naming column B
and C in sheet 1 or something)?

thanks for the help !
andy

 

[Bernie Deitrick]

Andy,

In C2 of sheet2

=VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2="S",2,3),FALSE)

HTH,
Bernie
MS Excel MVP

 

[Guest]

Bernie,

Thanks. but is there a way to avoid 2 and 3 in the If condition? is it
possible to refer to a column name instead so that if a user were to insert a
column before, the fomula still returns the right value?

Andy

 

[Bernie Deitrick]

If the user inserts a column before, all the columns would move over, so the formula would still
work. If the user inserts a column within, then you could replace:

IF(B2="S",2,3)

with

MATCH(B2,Sheet1!$A$1:$C$1,FALSE)