Sum for 26 worksheets on one workbook
 

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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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.

MO wrote:

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?
--
Thank you for your help
MO
Albany, NY

--

Dave Peterson

Sum for 26 worksheets on one workbook
 

=SUM(Sheet2:Sheet26!A1)

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

Sam

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

MO wrote:
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.

Sum for 26 worksheets on one workbook
 

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" wrote:
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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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?
--
Thank you for your help
MO
Albany, NY


"Sam Wilson" wrote:

=SUM(Sheet2:Sheet26!A1)

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

Sam

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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


"MO" wrote in message
...
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?
--
Thank you for your help
MO
Albany, NY


"Sam Wilson" wrote:

=SUM(Sheet2:Sheet26!A1)

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

Sam

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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?
--
Thank you for your help
MO
Albany, NY


"Max" wrote:

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" wrote:
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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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" wrote:
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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

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

--
Thank you for your help
MO
Albany, NY


"Max" wrote:

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" wrote:
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?
--
Thank you for your help
MO
Albany, NY

Sum for 26 worksheets on one workbook
 

Thank you Bob. I tried it and it did not work. Max provided me with the
answer. Until next time......mucho thanks.
--
Thank you for your help
MO
Albany, NY


"Bob Umlas" wrote:

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


"MO" wrote in message
...
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?
--
Thank you for your help
MO
Albany, NY


"Sam Wilson" wrote:

=SUM(Sheet2:Sheet26!A1)

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

Sam

"MO" wrote:

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?
--
Thank you for your help
MO
Albany, NY