Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
return a value from another cell
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
return a value from another cell
Andy,
In C2 of sheet2 =VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2="S",2,3),FALSE) HTH, Bernie MS Excel MVP "andy" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
return a value from another cell
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" wrote: Andy, In C2 of sheet2 =VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2="S",2,3),FALSE) HTH, Bernie MS Excel MVP "andy" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
return a value from another cell
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) -- HTH, Bernie MS Excel MVP "andy" wrote in message ... 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" wrote: Andy, In C2 of sheet2 =VLOOKUP(A2,Sheet1!$A$2:$C$6,IF(B2="S",2,3),FALSE) HTH, Bernie MS Excel MVP "andy" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make cell 2 return data if cell 1 contains text? | Excel Discussion (Misc queries) | |||
If it's not a space, return the cell contents. | Excel Worksheet Functions | |||
Select cell, Copy it, Paste it, Return to Previous cell | Excel Discussion (Misc queries) | |||
Find a value in a cell and return another value in a differant cel | Excel Discussion (Misc queries) | |||
return a cell contents as the result of usiing "small" | Excel Worksheet Functions |