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

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!!!!


Summary tab look like this:

Q4'14
BB Accrual Payment EB
Company1
Company2
Company3

Each tab looks like this:


Q3'14 Q4'14 Q1'15 Q2'15
BB 180,000
Accrual - 25,000
Payment (64,000)
EB - 141,000 - -

Welcome to the Forum, unfortunately:

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

http://www.excelforum.com/excel-prog...mmary-tab.html

Thread Closed.