Sum worksheets - w/o updating formula for new worksheets which are

  • Thread starter Thread starter Elgee
  • Start date Start date
E

Elgee

Hi -
I know how to sum fields of various worksheets. However, can I set the
formula so that if a new worksheet is added, the formula will automatically
update to include the new worksheet? (Data in the same cell as other
worksheets.)

Thank you!!
 
Include two dummy sheets in our workbook, say alpha and omega. Make alpha
the first and omega the last. Sum from alpha thru omega. When you add a new
worksheet, just add it AFTER alpha.

This will insure that the new worksheet is between alpha and omega and will
be included in the sum
 
If you have a blank worksheet named "first" and one named "last" and
position these so that they encompass the sheets you want to sum from
like a sandwich, they you can have a formula like:

=SUM(first:last!A1)

This will add all the A1 cells between and including those sheets. You
can move sheets into and out of the "sandwich" to model the effects of
these changes, and the formula remains the same.

Hope this helps.

Pete
 
MOST EXCELLENT!

Thank you!

Pete_UK said:
If you have a blank worksheet named "first" and one named "last" and
position these so that they encompass the sheets you want to sum from
like a sandwich, they you can have a formula like:

=SUM(first:last!A1)

This will add all the A1 cells between and including those sheets. You
can move sheets into and out of the "sandwich" to model the effects of
these changes, and the formula remains the same.

Hope this helps.

Pete
Click to expand...
 
FANTASTIC! Thank you!

Gary''s Student said:
Include two dummy sheets in our workbook, say alpha and omega. Make alpha
the first and omega the last. Sum from alpha thru omega. When you add a new
worksheet, just add it AFTER alpha.

This will insure that the new worksheet is between alpha and omega and will
be included in the sum
Click to expand...
 
Back
Top