Extract Data from multiple tabs in one workbook to a summary tab

[iceplant]

Hello! First thanks for any help here!!

I have more than 100 tabs in one workbook. They all format quite similar. What I need to do is to create a summary page to pull some data from each worksheet. I have a sample file attached.


For example, I have all the tab names listed in the summary page. I need to pull the data on BB row, Q4'14 column in each tab. The issue is that BB row is always in column B but not sure which row it ends at in each tab. Same as CYQ4'14 column. So I need to create a dynamic range to locate the number under BB row and Q4'14 column


I come up with indirect formula to extract numbers from each tab and use vlookup + match to lookup in a dynamic range, but it turns out not working. Not sure where it is wrong.

=VLOOKUP(C4,INDIRECT("'"&B5&"'!B:F"),MATCH(B3,INDIRECT("'"&B5&"'!1:100")))

Could anyone help on this ? If there is any good VBA code, that will be great too.

Thank you very much for your help!!!!!Really appreciate!!!!





-

 

[Hubert]

If the tables in each sheet are all the same structure.
(say Q4'14 is always in the next 2 columns after BB)
this should be fine solving your problem.
=OFFSET(INDIRECT("'"&$B5&"'!B1"),MATCH(C$4,INDIRECT("'"&$B5&"'!B1:B100"),)-1,2)

 

[iceplant]

If the tables in each sheet are all the same structure.
(say Q4'14 is always in the next 2 columns after BB)
this should be fine solving your problem.
=OFFSET(INDIRECT("'"&$B5&"'!B1"),MATCH(C$4,INDIRECT("'"&$B5&"'!B1:B100"),)-1,2)

Thank you for your reply!!!
What if Q4'14 is not always after the next column after BB? BB is always at column B but the position of Q4'14 actually is decided by the starting quarter.

Also, if I have about 400 tabs, is offset going to be calculate slow?

Really appreciate your help!!!

 

[Hubert]

If BB is always the first row of each table, you may try HLOOKUP + OFFSET:
=HLOOKUP($B$3,OFFSET(INDIRECT("'"&$B5&"'!B"&MATCH(C$4,INDIRECT("'"&$B5&"'!B1:B100"),)),-1,,10,100),2,)

Yes, I think so. 400 tabs will make it calculate slow. However, without using OFFSET, I have no idea knowing the location and size of each table in sheet.

I hope other people can have better idea.

 

[iceplant]

Thanks for your help Hubert!!

I got is solved by using VBA code. Here is the code and file.


Thanks for your help and time!!!