Adding numbers across multiple worksheets

[mcbride.o]

Hi

New to all these fancy functions and I need help. I have several spreadsheets, listing different weeks,eg., sheet 1 is Apr 1, sheet 2 is Apr 8, etc. All sheets are formatted exactly the same; however, each sheet contains different numbers. Eventhough the numbers change each week, the cell reference or location is always the same in the next sheet since the forms are formatted exactly the same each week. I would like to be able to total all of the combined worksheets on one separate worksheet. Please help.

 

[Frank Kabel]

Hi
if you want to sum your worksheets you may use a formula like
=SUM('sheet2:sheet10'!A1)
in cell A1 on your summary sheet

 

[Norman Harker]

Hi mcbride.o

Here's an example:

=SUM(Sheet1:Sheet9!A1:A1)

Or for your case:

=SUM('Apr 1:Apr 29'!A1:A1)

Note the use of the ' either end of the sheet names. This is needed
where sheet names contain spaces.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Norman Harker]

Hi O!

I think what you are wanting is:

=SUM('Apr 1:Apr 8'!C11:C11)+SUM('Apr 1:Apr 8'!G11:G11)



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Frank Kabel]

Hi
try
=SUM('Apr 1:Apr 29'!C11,'Apr 1:Apr 29'!G11)

 

[mcbride.o]

I tried the formula Frank and Norman and both worked fine in test phase. When I applied it to my spreadsheet, this is what happened. I have 21 worksheets. My first sheet is Mar 27 and Apr 3, my last worksheet is Dec 18 and Dec 25, the worksheet just before my last worksheet is Dec 4 and Dec 11.

The formula works well upto and including Dec 4 and Dec 11 as this =SUM('Ending Mar 27 and Apr 3:Ending Dec 4 and Dec 11'!C11,'Ending Mar 27 and Apr 3:Ending Dec 4 and Dec 11'!G11). Works perfect. However, if I try and change the formula to Dec 18 and Dec 25, (my last tab) I get a #REF! error. It seems to work only upto and including the next to last tab worksheet. Why....Help me please

"O

----- Frank Kabel wrote: ----

H
tr
=SUM('Apr 1:Apr 29'!C11,'Apr 1:Apr 29'!G11


--
Regard
Frank Kabe
Frankfurt, German


mcbride.o wrote

 

[Norman Harker]

Hi O!

Sounds to me like you have got an error in naming your sheets. There's
no reason why the formulas given should not work for the last sheets.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.