lookup with a twist

[frankandsteph]

So I have a dynamic lookup I need some help with. I have some vba code that heads out to a network drive and brings in multiple sheets from dozens of workbooks. It then creates a list of sheet names in column B of my 'summary' sheet. The next time I run it it clears out all the old sheets and brings them back in to this master workbook and recreates by list of sheet names.

What I would like to do is, after everything is copied in, place the last value in column C of all the sheets next to its name on my summary page. Seems simple, but since the tab names are dynamic I can't hard code into a lookup.

I am using this to grab the minimum value, but I can't seem to make the leap to get the last value in C
=IFERROR(MIN(INDIRECT(B4&"!I:I")),"")

Any thoughts?

-Frank

 

[mrshl9898]

If on row 12 =INDIRECT(B4&"!"&ADDRESS(12,3))

 

[frankandsteph]

You have been a great help, thank you!

 

[Aladin Akyurek]

Looks like:

=LOOKUP(9.99999999999999E+307,INDIRECT(B4&"!C:C"))