Using INDIRECT within functions
 

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!

Using INDIRECT within functions
 

This seems ok, tested he
=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
--
Max
Singapore
---
"Brian" wrote:
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!

Using INDIRECT within functions
 

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

"Max" wrote:

This seems ok, tested he
=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
--
Max
Singapore
---
"Brian" wrote:
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!

Using INDIRECT within functions
 

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.
--
Max
Singapore
---
"Brian" wrote:
Thanks - didn't realize it should be used twice in the formula. It makes
sense though.

"Max" wrote:

This seems ok, tested he
=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
--
Max
Singapore
---
"Brian" wrote:
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!

Using INDIRECT within functions
 

=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.

--
Biff
Microsoft Excel MVP


"Brian" wrote in message
...
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!