| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| One workbook with 62 worksheets | Excel Worksheet Functions | |||
| Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
| Can't add worksheets to a workbook. | Excel Discussion (Misc queries) | |||
| Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
| How do I build a workbook from the worksheets another workbook? | Excel Discussion (Misc queries) | |||
Linear Mode
