Pull data from 55 workbooks into one.

[wallington15]

Hi! I currently have 55 diferrent workbooks, each workbook is for an entity - Entity 1, Entity 2, Entity 3 etc. Within the workbook I have a tab which has a column titled 1 October Balance, this column might not be in the same column in each workbook but will be called the same name. I have a consolidated file where I need to pull in the 1 October Balance for each workbook. What is the quickest way to do this, index match?

Also if I use index match and some updates the individual entity workbook will it update in my consolidated file?

 

[Zot]

If you have this many workbook then I guess the best way is to use macro to fetch data from each workbook and consolidate it somewhere. The macro can search the column titled 1 October Balance automatically. If you update anything in any workbook, you have to run the program again to get new updated consolidated data though.

If you like the approach, then you need to provide layout of the sheet you want to extract data from, the sheet name (assuming workbook has many other sheets). How you want data to be consolidated. Is it another specific workbook. What data need to be collected, etc.?