Using combination of LOOKUP and INDIRECT

[blacklab1059]

I have multiple sheets - 1 is an index and the rest are project sheets 1801, 1802, 1803....
The index has a column "B" that lists all the project sheet names.
I am trying to determine the last populated date in each project sheet - also column B.
I am able to determine the last populated date with the following formula: =LOOKUP(2,1/('1819'!B:B<>""),'1819'!B:B) ~1819 is the project sheet.
I want to dynamically populate the project sheet # (1819) from the index column B.
I have successfully modified the second half of the formula as such: =LOOKUP(2,1/('1819'!B:B<>""),INDIRECT(B21&"!B:B"))
I am unable to get INDIRECT to work for the first half of the formula: =LOOKUP(2,1/('1819'!B:B<>"") to show the value of B21 (1819).
I have done a pretty thorough search but have been unable to come up with a solution.
Thoughts?

 

[James006]

Hello,

Have you tested the following :

=LOOKUP(2,1/(INDIRECT(B21&"!B:B")<>""),INDIRECT(B21&"!B:B"))

Hope this will help

 

[blacklab1059]

It worked!!! Thank you very much!

 

[Aladin Akyurek]

It's much faster if you invoke

=LOOKUP(9.99999999999999E+307,INDIRECT(B21&"!B:B"))

where B21 is a sheet name.

 

[James006]

@ Aladin,

If I am not mistaken ... there is a difference between Text and Values ...

 

[Aladin Akyurek]

@ Aladin,

If I am not mistaken ... there is a difference between Text and Values ...

OP: "I am trying to determine the last populated date in each project sheet - also column B."

If it's text, I'd suggest...

=LOOKUP(REPT("z",255),INDIRECT(B21&"!B:B"))

 

[James006]

@ Aladin,

Thanks for your insight ...

Does it mean that OP's formula is only to be used in generic cases ... whenever one does not know if he will retrieving Text or Value ...

 

[Aladin Akyurek]

@ Aladin,

Thanks for your insight ...

Does it mean that OP's formula is only to be used in generic cases ... whenever one does not know if he will retrieving Text or Value ...

In case the last value what we are after can be one of numeric, text, logicals...

Either:
=LOOKUP(9.99999999999999E+307,1/(B2:B7<>""),B2:B7)

Or:
=LOOKUP(9.99999999999999E+307,SEARCH("?*",B2:B7),B2:B7)

If the last value can be anything, including a formula blank...

=LOOKUP(9.99999999999999E+307,1/(1-ISBLANK(B2:B7)),B2:B7)

 

[James006]

@ Aladin,

Thanks a lot for your complete explanation ...

 

[Aladin Akyurek]

@ James

Thanks for the curiosity and kind feedback.