Connect data from hundreds of closed workbooks to one workbook

Dear Scientists,

I have a simple problem but I really can't find an elegant solution (not ctrl + c and ctrl v).

Workbook "2014 Sales.xlsx" must have 12 worksheets, one per month, which one must have 31 columns that gets data from workbooks 2014 01 01.xlsx; 2014 01 02.xlsx; 2014 01 03.xlsx;... and so on, as externals links. And I must say that, I can NOT work with 31 different workbooks all open at the same time!

I know that, there is no way to make INDIRECT (....) read from closed workbooks. Period.

Is there a simple way to make that work? Maybe by using =vlookup(....)...

I did a small example, but it gives you an idea.

Thanks and keep it easy, literally. 2014 01 01.xlsx2014 01 02.xlsx2014 01 03.xlsx2014 Sales.xlsx

What about the 2 sheets you have in each day worrkbook? You probably want the sum of every sheets for one specific shop. Don't you?
Why don't you have all of this in only one workbook, even one sheet?
Or at least have a full month in only one sheet adding columns like State and date to the actual Shop and Sales columns.

this formula will update even if workbooks are closed. But It will take some time to do this.
Change the actual path in the formula to your real workbooks' location.

Please Login or Register  to view this content.

You may want to try this macro that will open every files and extract the sum of the shop sales from every tabs it contains.
Look at the comments in the code for some specifics issues about this code.

Please Login or Register  to view this content.

Hope this will help.