Calculate sum in multiple files

  • Thread starter Thread starter Tomos
  • Start date Start date
T

Tomos

I have over 300 individual excel files that contain job sheets for sites
that my company visits. The layout of each file is identical and there
is a particular column in each file containing 5 cells that requires
summing.

I need to create a new file with site names (the file names same as
site names) in column A, and the sum of each of the 5 cells in all
these files.

Is there a way to do this without opening every single file and summing
the cells back to the new sheet?

Hope I've been clear enough ;)
 
You can reference a closed workbook easily enough

=SUM('C:\MyTest\[test 1.xls]Sheet2'!$A$1:$A$5)

but you will need to adopt for each, you can't put the names ina list and
use that, INDIRECT doesn't work with closed workbooks.
 
That's what I've been doing. It's just so time consuming typing the
individual file names in.

Is there any way to list all the file names in a folder then use the
sum formula to refer to the cell with the file name in for the
=SUM(XXXSheet2'!$A$1:$A$5) XXX bit?
 
No, that is the INDIRECT part I mentioned before, but it doesn't work with
closed workbooks.

There is a function available if you search Google called Pull, which reads
from closed workbooks in that manner, but that would probably take as much
effort as doing them all.

Just thought. Another way.

Put the list of file names in column A, File.xls, File 2.xls, etc.
Then put your formula in B1, but use XXXX for the filename, like so

=SUM('C:\[XXXX]Sheet1'!$A$1:$A$5)
Then run this bit of code

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To iLastRow
Cells(i, "B").Formula = Replace(Cells(i, "B").Formula, "XXXX",
Cells(i, "A").Formula)
Next i

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Tomos wrote...
That's what I've been doing. It's just so time consuming typing the
individual file names in.

Is there any way to list all the file names in a folder then use the
sum formula to refer to the cell with the file name in for the
=SUM(XXXSheet2'!$A$1:$A$5) XXX bit?
Click to expand...

If you're summing the same range of cells in the same named worksheet
in multiple files, then you may be better off using Data > Consolidate.
Select the cell where you want the sum to appear, run Data >
Consolidate, and enter a Reference like

<your path here>\[*.xls]SheetX!$Y$99

and Excel will place the sum of all XLS file's SheetX!Y99 values in the
active cell.
 
Thats useful to know actually! But I will need different sums from all
separate sheets rather than just one.

Thanks for all your help guys and gals, looks like its going to have to
be a manual jobbie.
 
Back
Top