Calculate sum in multiple files

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.

--
HTH

Bob Phillips

"Tomos" <Tomos.1u9mic_1124881505.5956@excelforum-nospam.com> wrote in
message news:Tomos.1u9mic_1124881505.5956@excelforum-nospam.com...
>
> 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
>
>
> --
> Tomos
> ------------------------------------------------------------------------
> Tomos's Profile:
http://www.excelforum.com/member.php...o&userid=26581
> View this thread: http://www.excelforum.com/showthread...hreadid=398527
>

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" <Tomos.1u9s2b_1124888705.9042@excelforum-nospam.com> wrote in
message news:Tomos.1u9s2b_1124888705.9042@excelforum-nospam.com...
>
> 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?
>
>
> --
> Tomos
> ------------------------------------------------------------------------
> Tomos's Profile:
http://www.excelforum.com/member.php...o&userid=26581
> View this thread: http://www.excelforum.com/showthread...hreadid=398527
>

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?

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.