Referring to a variable cell on another sheet

[PaladinWhite]

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!

 

[Gary]

=LOOKUP(2,1/(SheetB!R1:R65535<>""),SheetB!R1:R65535)

Press CTRL+SHIFT+ENTER. not just ENTER.

Hope this helps.
Thanks
Gaurav

 

[Gary]

Sorry...this would work even if you press just ENTER. Sorry for the
confusion.

 

[PaladinWhite]

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?

 

[PaladinWhite]

Ah, wait - it occurs to me that 65,535 is the largest number that can be
represented in unsigned 16-bit binary...

 

[PaladinWhite]

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.