how do i add new sheets to an exsisting formula

[Ken Wright]

Example of your formula.............

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Jcarriere]

is there a way to automatically add a new sheet to an exisiting formula when
you add a new sheet to the book

 

[Jim Cone]

J,

If you use a formula like...

=SUM(Sheet1:Sheet5!D25)

then if you add a sheet between Sheet1 and Sheet5 then the
new sheet will be included in the formula.

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Jcarriere" <[email protected]>
wrote in message
is there a way to automatically add a new sheet to an exisiting formula when
you add a new sheet to the book

 

[Jcarriere]

ok like i have multiple sheets in my book and i have a sum formula going to
go to a sheet that i have called MTD (month to date) =SUM(Sheet3!A1+Sheet2A1)
but i add new sheets every day and am wondering if you are able to have the
new sheet automatically go into the formula when you insert the new sheet
with out have to add it in manually everyday
Thank you
Jason

 

[Ken Wright]

Create your file and get one sheet exactly the way you want it to be (Take
your time and get it right, because now is the time to do this) - Now right
click on the tab and select move or copy and then tick the little box that
says create a copy. Now repeat that until you have as many sheets as you
need PLUS at least 3 more. You can select say 2,3,4,5,6,7.... sheet tabs at
once and then do move or copy..... and it will copy as many as you have
selected. Now ignore the first 2 sheets and start naming them all say
Week 1, Week 2 or a,b,c etc and so on. Name the first sheet Summary,
the second sheet START, and your very last sheet FINISH.

Now click on your Summary sheet and in A1 put
=SUM(START:FINISH!A1) which will pull in any data from A1 pulling from START
sheet to FINISH sheet and including any sheets in between. As long as any
sheets you add to the file are in between START and FINISH sheets, they will
get pulled into the total, no matter what you call them. Now hide your
START and FINISH
sheets.

Copy the cell A1 and then paste special as formulas into any cells that you
need to add up all the sheets on the Summary page.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Jcarriere]

Thank you now here is a dumb question how do i hide those two tabs and just
those two
Jason

 

[Ken Wright]

Why would you? I'll tell you how, but first I'll tell you why i wouldn't
(or at least not the last one).

The only way the data gets pulled into the summary is when it is sat between
those two tabs. If you hide them, how do you know when you insert another
that it is to the left of the hidden one at the end, and not the right?
Easy mistake to make, and not noticeable till your numbers are screwed up.

OK, assuming you still want to, simply click on a sheet and hit Format /
Sheet / Hide and you will hide just that sheet.

Regards
Ken.....................