Sum for 26 worksheets on one workbook

[MO]

Hope someone can help this poor soul!

I have 26 worksheets on one workbook that I need to add one cell from EACH
worksheet. When I try to add them all, there isn't enough room in the
formula box (it covers the formula and I can't choose the cell).

Do you know a way to accomplish this?

 

[Dave Peterson]

Is it always the same address?

If yes, then I add a sheet to the far left and far right (called Start and
Finish) with the data sheets in between--like a sandwich.

Then I use:
=sum('start:finish'!a1)

I can drag sheets in and out of this sandwich to play what-if games.

If the cells are different addresses, you could use:
=sum(some sheets)+sum(other sheets)+sum(more sheets)

or even dedicate a cell on each sheet and use the sandwich method.

 

[Sam Wilson]

=SUM(Sheet2:Sheet26!A1)

would work if your cell is in the same location on each sheet.

Sam

 

[Glenn]

Hope someone can help this poor soul!

I have 26 worksheets on one workbook that I need to add one cell from EACH
worksheet. When I try to add them all, there isn't enough room in the
formula box (it covers the formula and I can't choose the cell).

Do you know a way to accomplish this?

If the problem is the expanding formula box is covering the cells you want to
include, click "Restore Window". It's the button between "Minimize Window" and
"Close Window". Then resize the workbook window lower inside the Application
window.

 

[Max]

If that one cell to be summed is the SAME cell in each sheet,
then a "sandwich" method like this would work

Insert 2 new sheets, name them simply as: x, y
Move x to be leftmost sheet, y to the extreme right of the 26 sheets to be
summed
Then in your summary sheet (which has to be outside of the x-y sandwich)
you could use eg: =SUM(x:y!B2)
to return the sum of cell B2 in all 26 sheets
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[MO]

Sam,
Thank you. Yes, the cell is in the same location on each sheet.

I tried it =SUM('08-09 ED PYMNTS IN 09-10'!H3:'09-10-FSCL ADMN-BGPMNT IN
09-10'!H3) and got VALUE as an answer. Did I do something incorrect?

 

[Bob Umlas]

=SUM('08-09 ED PYMNTS IN 09-10':'09-10-FSCL ADMN-BGPMNT IN

 

[MO]

Max,
The only problem is that the first tab is a negative number but the sandwich
is treating it like a positive number. Other tabs (in the future) may have
negative numbers. Is there a way to make this work with the sandwich method?

 

[Max]

Afraid I'm not really sure what's the current issue that you're facing. The
names of the 26 sheets sandwiched between x and y is immaterial, the names
don't figure in the formula. And that formula: =SUM(x:y!B2)
would simply add up the numbers in cell B2 in each sheet,
eg: 2 + 3 + (-3) + 4 + (-7) + ....
assuming that these are the numbers (could be positive or negative) that's
in cell B2 in the 26 sheets
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[MO]

Max,
MY MISTAKE.....It worked. Many Many thanks

 

[MO]

Thank you Bob. I tried it and it did not work. Max provided me with the
answer. Until next time......mucho thanks.