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]

How's this? =INDIRECT(B4&"!"&ADDRESS(COUNTA(INDIRECT(B4&"!C:C")),3))

 

[frankandsteph]

It is bringing back a date from column C, just no the last date, 12 rows from the bottom to be exact! So almost there

 

[mrshl9898]

Are there blanks in that column? Choose a column without blanks, maybe column A?

=INDIRECT(B4&"!"&ADDRESS(COUNTA(INDIRECT(B4&"!A:A")),3))

 

[frankandsteph]

Sorry, you lost me, the data is in C, why switch to A? The data in C starts around C10

 

[frankandsteph]

I think I understand what you are asking, the countA is also counting the balnks at the top of my data set. The bad thing is that my date and data columns all start a ways down and nothing has a full column set of data!

 

[frankandsteph]

Actually, I confirmed it always starts in C12 if that helps!

 

[mrshl9898]

Without a complete column I can't think of a method for finding the last row without VBA or a helper column to make a complete row. It's ok if the data starts on row 12, but this method requires a complete row beneath that.

 

[mrshl9898]

Just noticed you mentioned starting on row 12 and being 12 rows off the result. Makes me think column C is complete =INDIRECT(B4&"!"&ADDRESS(COUNTA(INDIRECT(B4&"!C:C"))+11,3))

 

[frankandsteph]

Just noticed you mentioned starting on row 12 and being 12 rows off the result. Makes me think column C is complete =INDIRECT(B4&"!"&ADDRESS(COUNTA(INDIRECT(B4&"!C:C"))+11,3))

PERFECT..Thank You

What would I change to grab the 1st piece of data in C?