Summing one cell from multiple workbooks in a folder!

[hmapps]

Hi All,

I have about 200 different files -- they all have an identical worksheet (same tab name) with data in the same places - I want to create another worksheet in a different workbook that takes the sum of every cell in every one of those worksheets (basically another identical sheet with the sum in each cell). I have slighlty attempted both VBA and indirect.exe, with not much luck.

I would like it to be linked to the data as each file gets updated on a constant basis - also the specific file names may change as well - so ideally it would reference the whole directory, not just exact file names.

Please let me know if anyone has an idea on how to do this!

Thank you so much for your help!!!

-Heather

 

[General Ledger]

Wow! You have a lot of stuff.

I would try to approach the situation differently. Is it possible to put each of the 200 files on separate sheets of a single file? Then you can use a sum through formula. Something like =SUM(Jan:Dec!A1) which sums the values in cell A1 on all sheets between and including the sheets named Jan and Dec.

Advantages of this approach include:
No need for VBA.
No links to outside workbooks.
No need to worry about changing file names.
A change to any data on any of the sheets will automatically and instantly update the summary sheet.

Best of luck,

G/L