Updating a formula to include values from a newly added tab

travis.m.roberts

Board Regular
Joined
Jul 7, 2009
Messages
62
Is there a way to automatically have a formula updated to include any tabs that are added? I have a spreadsheet that starts out with two tabs (summary page and one heavily formatted sheet). The summary page has a bunch of formulas on it that I would like to have updated to include values from a new tab each time it is inserted. For instance if the formula is =SUM('090800'!Q35) and I add a tab named 090900 I need the formula on the summary page to be updated as such: =SUM('090800'!Q35,'090900'!Q35). Is that possible?
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

taigovinda

Well-known Member
Joined
Mar 28, 2007
Messages
2,639
You can add two blank tabs called FIRST and LAST. Then put all the sheets to be summed in between the two tabs, and use this formula:

=SUM('First:Last'!Q35)

...the FIRST and LAST tabs can be hidden also.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Insert a New Sheet immediately after your Summary Sheet and name it First
Insert another new sheet and drag it into the last position to the right and name it Last

The summary sheet could have this formula in it:

=SUM(First:Last!Q35)

All the data sheet should be BETWEEN the First and the Last << Careful on this; If you
do -- this will do what you want..

Good Luck,

Jim
 

Watch MrExcel Video

Forum statistics

Threads
1,123,115
Messages
5,599,809
Members
414,341
Latest member
Mohammedsobhey

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