Home |
Search |
Today's Posts |
#1
|
|||
|
|||
open ended multi page sum
I have a workbook with multiple worksheets that are identical in format. I would like to add a summary sheet that adds all of the values of the other pages, i.e., cell A1 on the summary page would be the sum of all of the A1 cells on the other pages. The sheet also has text in many boxes, but I could simply retype these on the summary page. The problem is that we do not know how many pages will be summed up at any given time. It could be anywhere from 2 to 100 depending upon how many people input data for this project. Not knowing ahead of time how many "new" sheets will be provided is the problem. Any suggestions (equations?) that will solve this? I would prefer not having to pre-save 100 sheets in advance of getting input and have to copy and paste on to each sheet, or have to manually change the summing equation everytime a new sheet arrives. I would prefer that the individual sheets be inserted into the workbook where the summary sheet is and the summing equations only look for "filled in" sheets. -- AlZee ------------------------------------------------------------------------ AlZee's Profile: http://www.excelforum.com/member.php...o&userid=15906 View this thread: http://www.excelforum.com/showthread...hreadid=273888 |
#2
|
|||
|
|||
AlZee
Make the Ssummary sheet the first sheet. After that sheet insert a new sheet and name in StartSht. At end of all sheets insert a new sheet named EndSht. On Summary sheet enter =Sum(StartSht:EndSht!A1) When inserting new sheets after this, make sure to insert between StartSht and EndSht. Gord Dibben Excel MVP On Sat, 30 Oct 2004 14:22:32 -0500, AlZee wrote: I have a workbook with multiple worksheets that are identical in format. I would like to add a summary sheet that adds all of the values of the other pages, i.e., cell A1 on the summary page would be the sum of all of the A1 cells on the other pages. The sheet also has text in many boxes, but I could simply retype these on the summary page. The problem is that we do not know how many pages will be summed up at any given time. It could be anywhere from 2 to 100 depending upon how many people input data for this project. Not knowing ahead of time how many "new" sheets will be provided is the problem. Any suggestions (equations?) that will solve this? I would prefer not having to pre-save 100 sheets in advance of getting input and have to copy and paste on to each sheet, or have to manually change the summing equation everytime a new sheet arrives. I would prefer that the individual sheets be inserted into the workbook where the summary sheet is and the summing equations only look for "filled in" sheets. |
#3
|
|||
|
|||
Put in two dummy sheets: one in front, one at end
Let's call then Alpha and Omega Have Summary sheet before Alpha or after Omega Use =SUM(Alpha:Omega!A1) to sum -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "AlZee" wrote in message ... I have a workbook with multiple worksheets that are identical in format. I would like to add a summary sheet that adds all of the values of the other pages, i.e., cell A1 on the summary page would be the sum of all of the A1 cells on the other pages. The sheet also has text in many boxes, but I could simply retype these on the summary page. The problem is that we do not know how many pages will be summed up at any given time. It could be anywhere from 2 to 100 depending upon how many people input data for this project. Not knowing ahead of time how many "new" sheets will be provided is the problem. Any suggestions (equations?) that will solve this? I would prefer not having to pre-save 100 sheets in advance of getting input and have to copy and paste on to each sheet, or have to manually change the summing equation everytime a new sheet arrives. I would prefer that the individual sheets be inserted into the workbook where the summary sheet is and the summing equations only look for "filled in" sheets. -- AlZee ------------------------------------------------------------------------ AlZee's Profile: http://www.excelforum.com/member.php...o&userid=15906 View this thread: http://www.excelforum.com/showthread...hreadid=273888 |
#4
|
|||
|
|||
Ssummary should read Summary.
name in StartSht should be name it StartSht. Any other typos I may have missed? Gord On Sat, 30 Oct 2004 12:40:13 -0700, Gord Dibben <gorddibbATshawDOTca wrote: AlZee Make the Ssummary sheet the first sheet. After that sheet insert a new sheet and name in StartSht. At end of all sheets insert a new sheet named EndSht. On Summary sheet enter =Sum(StartSht:EndSht!A1) When inserting new sheets after this, make sure to insert between StartSht and EndSht. Gord Dibben Excel MVP On Sat, 30 Oct 2004 14:22:32 -0500, AlZee wrote: I have a workbook with multiple worksheets that are identical in format. I would like to add a summary sheet that adds all of the values of the other pages, i.e., cell A1 on the summary page would be the sum of all of the A1 cells on the other pages. The sheet also has text in many boxes, but I could simply retype these on the summary page. The problem is that we do not know how many pages will be summed up at any given time. It could be anywhere from 2 to 100 depending upon how many people input data for this project. Not knowing ahead of time how many "new" sheets will be provided is the problem. Any suggestions (equations?) that will solve this? I would prefer not having to pre-save 100 sheets in advance of getting input and have to copy and paste on to each sheet, or have to manually change the summing equation everytime a new sheet arrives. I would prefer that the individual sheets be inserted into the workbook where the summary sheet is and the summing equations only look for "filled in" sheets. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
why would page 1 of an excel worksheet print smaller of page 2 if. | Excel Discussion (Misc queries) | |||
Excel Files Won't Open From Shortcut | Excel Discussion (Misc queries) | |||
How do I get my personal macro worksheet to open whenever I open . | Excel Discussion (Misc queries) | |||
Cannot open a file that Excel says is open | Excel Discussion (Misc queries) | |||
Open page to row 1 instead of row 36 | Excel Discussion (Misc queries) |