software

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 (quot;Squot; refers to column B in sheet 1, quot;Cquot; refers to column C
in sheet 1), i.e. ID can be either quot;Squot; or quot;Cquot;.

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

Andy,

In C2 of sheet2

=VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2=quot;Squot;,2,3),FALSE)

HTH,
Bernie
MS Excel MVPquot;andyquot; gt; wrote in message
...
gt; hi all,
gt;
gt; i have a problem returning a value from another cell.
gt;
gt; in sheet 1, i have:
gt; column A 'ID' : 1, 2, 3, 4, 5
gt; column B 'S' : a, b, c, d, e
gt; column C 'C' : A, B, C, D, E
gt; this is the basis for the other page, it should be possible to return these
gt; different values into the other page.
gt;
gt; in sheet 2 i have:
gt; column A : 1, 3, 1, 5, 4, ... (the same ID values as in sheet 1)
gt; column B : S or C (quot;Squot; refers to column B in sheet 1, quot;Cquot; refers to column C
gt; in sheet 1), i.e. ID can be either quot;Squot; or quot;Cquot;.
gt;
gt; column C : here's my problem.
gt;
gt; i want to add a formula that returns the value from either column B or C
gt; from sheet 1 that corresponds with the value in column A of sheet 2.
gt;
gt; example:
gt; SHEET 2:
gt; A2 = 3
gt; B2 = S
gt; than the value in C2 should be 'c'.
gt;
gt; on the otherhand, if:
gt; SHEET 2:
gt; A2 = 3
gt; B2 = C
gt; than the value in C2 should be 'C'.
gt;
gt; i managed to solve the problem via vlookup, but the thing is that if
gt; somebody inserts a column in sheet 1, the formula doesn't work anymore.
gt; is there a 'safer' possibility that copes with this (e.g. by naming column B
gt; and C in sheet 1 or something)?
gt;
gt; thanks for the help !
gt; andy
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

quot;Bernie Deitrickquot; wrote:

gt; Andy,
gt;
gt; In C2 of sheet2
gt;
gt; =VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2=quot;Squot;,2,3),FALSE)
gt;
gt; HTH,
gt; Bernie
gt; MS Excel MVP
gt;
gt;
gt; quot;andyquot; gt; wrote in message
gt; ...
gt; gt; hi all,
gt; gt;
gt; gt; i have a problem returning a value from another cell.
gt; gt;
gt; gt; in sheet 1, i have:
gt; gt; column A 'ID' : 1, 2, 3, 4, 5
gt; gt; column B 'S' : a, b, c, d, e
gt; gt; column C 'C' : A, B, C, D, E
gt; gt; this is the basis for the other page, it should be possible to return these
gt; gt; different values into the other page.
gt; gt;
gt; gt; in sheet 2 i have:
gt; gt; column A : 1, 3, 1, 5, 4, ... (the same ID values as in sheet 1)
gt; gt; column B : S or C (quot;Squot; refers to column B in sheet 1, quot;Cquot; refers to column C
gt; gt; in sheet 1), i.e. ID can be either quot;Squot; or quot;Cquot;.
gt; gt;
gt; gt; column C : here's my problem.
gt; gt;
gt; gt; i want to add a formula that returns the value from either column B or C
gt; gt; from sheet 1 that corresponds with the value in column A of sheet 2.
gt; gt;
gt; gt; example:
gt; gt; SHEET 2:
gt; gt; A2 = 3
gt; gt; B2 = S
gt; gt; than the value in C2 should be 'c'.
gt; gt;
gt; gt; on the otherhand, if:
gt; gt; SHEET 2:
gt; gt; A2 = 3
gt; gt; B2 = C
gt; gt; than the value in C2 should be 'C'.
gt; gt;
gt; gt; i managed to solve the problem via vlookup, but the thing is that if
gt; gt; somebody inserts a column in sheet 1, the formula doesn't work anymore.
gt; gt; is there a 'safer' possibility that copes with this (e.g. by naming column B
gt; gt; and C in sheet 1 or something)?
gt; gt;
gt; gt; thanks for the help !
gt; gt; andy
gt;
gt;
gt;

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=quot;Squot;,2,3)

with

MATCH(B2,Sheet1!$A$1:$C$1,FALSE)--
HTH,
Bernie
MS Excel MVPquot;andyquot; gt; wrote in message
...
gt; Bernie,
gt;
gt; Thanks. but is there a way to avoid 2 and 3 in the If condition? is it
gt; possible to refer to a column name instead so that if a user were to insert a
gt; column before, the fomula still returns the right value?
gt;
gt; Andy
gt;
gt; quot;Bernie Deitrickquot; wrote:
gt;
gt;gt; Andy,
gt;gt;
gt;gt; In C2 of sheet2
gt;gt;
gt;gt; =VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2=quot;Squot;,2,3),FALSE)
gt;gt;
gt;gt; HTH,
gt;gt; Bernie
gt;gt; MS Excel MVP
gt;gt;
gt;gt;
gt;gt; quot;andyquot; gt; wrote in message
gt;gt; ...
gt;gt; gt; hi all,
gt;gt; gt;
gt;gt; gt; i have a problem returning a value from another cell.
gt;gt; gt;
gt;gt; gt; in sheet 1, i have:
gt;gt; gt; column A 'ID' : 1, 2, 3, 4, 5
gt;gt; gt; column B 'S' : a, b, c, d, e
gt;gt; gt; column C 'C' : A, B, C, D, E
gt;gt; gt; this is the basis for the other page, it should be possible to return these
gt;gt; gt; different values into the other page.
gt;gt; gt;
gt;gt; gt; in sheet 2 i have:
gt;gt; gt; column A : 1, 3, 1, 5, 4, ... (the same ID values as in sheet 1)
gt;gt; gt; column B : S or C (quot;Squot; refers to column B in sheet 1, quot;Cquot; refers to column C
gt;gt; gt; in sheet 1), i.e. ID can be either quot;Squot; or quot;Cquot;.
gt;gt; gt;
gt;gt; gt; column C : here's my problem.
gt;gt; gt;
gt;gt; gt; i want to add a formula that returns the value from either column B or C
gt;gt; gt; from sheet 1 that corresponds with the value in column A of sheet 2.
gt;gt; gt;
gt;gt; gt; example:
gt;gt; gt; SHEET 2:
gt;gt; gt; A2 = 3
gt;gt; gt; B2 = S
gt;gt; gt; than the value in C2 should be 'c'.
gt;gt; gt;
gt;gt; gt; on the otherhand, if:
gt;gt; gt; SHEET 2:
gt;gt; gt; A2 = 3
gt;gt; gt; B2 = C
gt;gt; gt; than the value in C2 should be 'C'.
gt;gt; gt;
gt;gt; gt; i managed to solve the problem via vlookup, but the thing is that if
gt;gt; gt; somebody inserts a column in sheet 1, the formula doesn't work anymore.
gt;gt; gt; is there a 'safer' possibility that copes with this (e.g. by naming column B
gt;gt; gt; and C in sheet 1 or something)?
gt;gt; gt;
gt;gt; gt; thanks for the help !
gt;gt; gt; andy
gt;gt;
gt;gt;
gt;gt;