SUM new created worksheets

RobbertH

Active Member
Joined
Apr 29, 2008
Messages
310
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Code:
'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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top