SUM new created worksheets

[RobbertH]

Hi All,

I am wondering if the following is possible. I have developed two models. One Model is per country and the other is per Region. The two models are named "Country Model" and "Region Model" respectively. To fill the model I copy the model in a new worksheet and fill it with the appropriate information. The new worksheet is named after the selected country or region. (The country is a lower level than the region which is basically a group of countries)

In a third worksheet I want to keep a sum of all created worksheets (so countries and regions together).

Is it possible to have the sum automatically adding a new created worksheet? nb the two models are identical in size and reference to a cell.

The outcome should look like this
The current sum in the Total Worksheet:

="Italy Model"!C1+"UK Model"!C1+"Africa Model"C1

Now i add a new worksheet named "USA Model"!

The SUM in the Total worksheet should now look like:

="Italy Model"!C1+"UK Model"!C1+"Africa Model"C1+"USA Model"!C1

I hope you have a solution. Thanks a lot for any help!!

Robbert

 

[Aladin Akyurek]

You can create 2 additional sheets, named First and Last, place all the relevant sheets between these two, and invoke:

=SUM(First:Last!C1)

 

[Colin Legg]

Hi Robbert,

One idea is to use hidden "buffer" sheets.

Suppose you have a workbook with six worksheets in this order:

Total
Sheet2
Italy Model
UK Model
Africa Model
Sheet6


Sheet2 and Sheet6 are just blank worksheets.


In your Total worksheet, you replace your current formula with this:

'current formula
="Italy Model"!C1+"UK Model"!C1+"Africa Model"C1
 
'change to
=SUM(Sheet2:Sheet6!C1)

Now you HIDE Sheet2 and Sheet6 using Format | Sheet | Hide.



Now, let's test the set up.
Add a new worksheet after the Total worksheet.
Rename it USA Model.
Put in a value into cell C1.

Check the total worksheet. The formula will account for the new sheet.


Of course, you can call the "buffer" sheets whatever names you want, and you can even use named ranges to make the formulas in the Total worksheet more meaningful.


Does that help?



EDIT: Okay, Aladin beat me to it!

 

[RobbertH]

Great guys!!!!! Works perfect!!!!!! Thanks