Using INDIRECT within functions

[Brian]

I learned a bit from JLathman in a previous post but tried to use the same
methodology with this formula (also learned from this Group to get the last
entered value in a column) without much luck.

The original formula was this with 2009 sheet being static:

=LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)

Trying to make it dynamic I tried the following without success:

=LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 &
"!$H$3:$H$65536)"))

Did I miss something or are the double quotes after the <> screwing this up.
If so, what's the correct way around it!

Thanks in advance!

 

[Max]

This seems ok, tested here
=LOOKUP(2,1/(INDIRECT("'"&O1&"'!H3:H65536")<>""),INDIRECT("'"&O1&"'!H3:H65536"))
where input in O1 is: 2009
voila? hit the YES below

You wrap INDIRECT to resolve the range bit of it

 

[Brian]

Thanks - didn't realize it should be used twice in the formula. It makes
sense though.

 

[Max]

Welcome. As mentioned, we wrap INDIRECT to resolve the ranges part of it,
where ranges would be textstrings formed via concats (using the & operator)
pointing to cells housing the sheetnames, etc for flexibility.

 

[T. Valko]

=LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536)

Are you wanting the last entry in the range of a *specific* data type? Do
you want the last numeric value in the range? Do you want the last text
value in the range? Or, as your formula will do, do want the last entry in
the range whether it's numeric or text?

If you want a *specific* data type then there are more efficient ways to do
it.