Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
In a cell on Sheet A, I want to display the value of a cell on Sheet B. I
know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
=LOOKUP(2,1/(SheetB!R1:R65535<""),SheetB!R1:R65535)
Press CTRL+SHIFT+ENTER. not just ENTER. Hope this helps. Thanks Gaurav "PaladinWhite" wrote in message ... In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
Sorry...this would work even if you press just ENTER. Sorry for the
confusion. "PaladinWhite" wrote in message ... In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
Cool, it works - got a minute to explain HOW in the world it works?
I understand the basic format of LOOKUP() - what are the 65535s and the <"" for? "Gary" wrote: Sorry...this would work even if you press just ENTER. Sorry for the confusion. "PaladinWhite" wrote in message ... In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
Ah, wait - it occurs to me that 65,535 is the largest number that can be
represented in unsigned 16-bit binary... "PaladinWhite" wrote: Cool, it works - got a minute to explain HOW in the world it works? I understand the basic format of LOOKUP() - what are the 65535s and the <"" for? "Gary" wrote: Sorry...this would work even if you press just ENTER. Sorry for the confusion. "PaladinWhite" wrote in message ... In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Referring to a variable cell on another sheet
Alright, this problem has become a little bit more complex, and I need some
more help... I'm working in two cells in SheetA - A2, where this new formula will go, and A1, which determines A2's value. I was constrained to Row R of SheetB before - I no longer know the row that I need beforehand. Instead, I need to do a LOOKUP, checking Row1 of SheetB for a value that matches the value of cell A1 in the current sheet. Then I need to return the value of the last nonblank cell in that row (there won't be any blank cells between filled cells, so the row of the last filled cell will equal COUNTA(that column)). So if A1's value is "Purple", and SheetB looks like this: Blue | Purple | Green 185 | 24812 | 3481 571 | 48194 | 1734 281 | 48194 | 5819 556 | 58602 | .... I need to return the value 58602. Thanks again to anyone that takes the time to help me out. "PaladinWhite" wrote: Ah, wait - it occurs to me that 65,535 is the largest number that can be represented in unsigned 16-bit binary... "PaladinWhite" wrote: Cool, it works - got a minute to explain HOW in the world it works? I understand the basic format of LOOKUP() - what are the 65535s and the <"" for? "Gary" wrote: Sorry...this would work even if you press just ENTER. Sorry for the confusion. "PaladinWhite" wrote in message ... In a cell on Sheet A, I want to display the value of a cell on Sheet B. I know the column that it is in (R), but not the row - the row should be equal the last row in that column which has a nonblank value. In other words, if the first six rows of column R on Sheet B have values, then I want to display the value of 'Sheet B'!R6. I think I can do this using INDIRECT(something) and COUNTA('Sheet B'!R:R), but I can't seem to fit them together in the correct manner. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to Sheet locations in a formula | Excel Worksheet Functions | |||
Referring to a variable file name | Excel Discussion (Misc queries) | |||
Formula referring to a different sheet | Excel Discussion (Misc queries) | |||
Cell reference - for the sheet name, can I use a variable? | Excel Discussion (Misc queries) | |||
referring to previous sheet | Excel Discussion (Misc queries) |