Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference a cell depending on the value in another cell??
Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of
a cell in Sheet1. How do I do this? For example, in Sheet1, cell A1 has the value "5". In cell A2, I want it to retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell A1). Thus, if I change the value in cell A1 to 6, cell A2 should then retrieve the data of the in cell B6 in Sheet2. The code in cell A2 would be something like: =Sheet2!B(Sheet1!A1) but of course that syntax doesn't work!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference a cell depending on the value in another cell??
On Jan 15, 2:04*pm, wb198 wrote:
Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of a cell in Sheet1. * How do I do this? For example, in Sheet1, cell A1 has the value "5". *In cell A2, I want it to retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell A1). *Thus, if I change the value in cell A1 to 6, cell A2 should then retrieve the data of the in cell B6 in Sheet2. The code in cell A2 would be something like: =Sheet2!B(Sheet1!A1) but of course that syntax doesn't work!! Use indirect: in Cell A2 =indirect("Sheet2!B" & A1) hth Carlo |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference a cell depending on the value in another cell??
Another one:
=INDEX(Sheet2!B:B,A1) -- Biff Microsoft Excel MVP "wb198" wrote in message ... Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of a cell in Sheet1. How do I do this? For example, in Sheet1, cell A1 has the value "5". In cell A2, I want it to retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell A1). Thus, if I change the value in cell A1 to 6, cell A2 should then retrieve the data of the in cell B6 in Sheet2. The code in cell A2 would be something like: =Sheet2!B(Sheet1!A1) but of course that syntax doesn't work!! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference a cell depending on the value in another cell
thanks greatly.
now is there a way to go a bit further and have it look for the value in cell A1 in a column in Sheet2, and then return the value from another cell in the same row in Sheet2 as the reference value it found? e.g. Sheet1: Cell A1 : Value is '5' Cell A2 : =INDEX..... Sheet2: Cell B1: Value is '7' Cell C1: Value is 'Sheep' Cell B2: Value is '5' Cell C2: Value is 'Dog' Cell B3 : Value is '9' Cell C3: Value is 'Cow' Cell A2 in Sheet1 will thus reference the value of Cell A1 ('5'), look for it in Column B of Sheet2, find it in Cell B2, and then return the value of Cell C2 ('Dog'). Thanks!! "T. Valko" wrote: Another one: =INDEX(Sheet2!B:B,A1) -- Biff Microsoft Excel MVP "wb198" wrote in message ... Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of a cell in Sheet1. How do I do this? For example, in Sheet1, cell A1 has the value "5". In cell A2, I want it to retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell A1). Thus, if I change the value in cell A1 to 6, cell A2 should then retrieve the data of the in cell B6 in Sheet2. The code in cell A2 would be something like: =Sheet2!B(Sheet1!A1) but of course that syntax doesn't work!! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to reference a cell depending on the value in another cell
Try this:
=VLOOKUP(A1,Sheet2!B:C,2,0) -- Biff Microsoft Excel MVP "wb198" wrote in message ... thanks greatly. now is there a way to go a bit further and have it look for the value in cell A1 in a column in Sheet2, and then return the value from another cell in the same row in Sheet2 as the reference value it found? e.g. Sheet1: Cell A1 : Value is '5' Cell A2 : =INDEX..... Sheet2: Cell B1: Value is '7' Cell C1: Value is 'Sheep' Cell B2: Value is '5' Cell C2: Value is 'Dog' Cell B3 : Value is '9' Cell C3: Value is 'Cow' Cell A2 in Sheet1 will thus reference the value of Cell A1 ('5'), look for it in Column B of Sheet2, find it in Cell B2, and then return the value of Cell C2 ('Dog'). Thanks!! "T. Valko" wrote: Another one: =INDEX(Sheet2!B:B,A1) -- Biff Microsoft Excel MVP "wb198" wrote in message ... Hi, I want to retrieve the data in a a cell in Sheet2, based on the value of a cell in Sheet1. How do I do this? For example, in Sheet1, cell A1 has the value "5". In cell A2, I want it to retrieve the data in cell B5 of Sheet2 (the 5 coming from the value in cell A1). Thus, if I change the value in cell A1 to 6, cell A2 should then retrieve the data of the in cell B6 in Sheet2. The code in cell A2 would be something like: =Sheet2!B(Sheet1!A1) but of course that syntax doesn't work!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I reference a range of cells depending on another cell valu | Excel Discussion (Misc queries) | |||
Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
Dynamically locking a cell depending upon value of another cell. | Excel Discussion (Misc queries) | |||
How can i change cell colour depending on month of date in cell? | Excel Discussion (Misc queries) | |||
problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions |