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

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.

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

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.