how do i add new sheets to an exsisting formula

[Jcarriere]

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

[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" <Jcarriere@discussions.microsoft.com> wrote in message
news:D1236054-957F-480A-AB8B-DFB085A70341@microsoft.com...
> 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" <Jcarriere@discussions.microsoft.com>
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" wrote:

> 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" <Jcarriere@discussions.microsoft.com> wrote in message
> news:D1236054-957F-480A-AB8B-DFB085A70341@microsoft.com...
> > is there a way to automatically add a new sheet to an exisiting formula
> > when
> > you add a new sheet to the book
> >
>
>
>

[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" <Jcarriere@discussions.microsoft.com> wrote in message
news:2CECD12D-074F-4D49-8E32-17CD904A7D8D@microsoft.com...
>
> 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" wrote:
>
>> 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" <Jcarriere@discussions.microsoft.com> wrote in message
>> news:D1236054-957F-480A-AB8B-DFB085A70341@microsoft.com...
>> > is there a way to automatically add a new sheet to an exisiting formula
>> > when
>> > you add a new sheet to the book
>> >
>>
>>
>>

[Jcarriere]

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

"Ken Wright" wrote:

> 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" <Jcarriere@discussions.microsoft.com> wrote in message
> news:2CECD12D-074F-4D49-8E32-17CD904A7D8D@microsoft.com...
> >
> > 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" wrote:
> >
> >> 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" <Jcarriere@discussions.microsoft.com> wrote in message
> >> news:D1236054-957F-480A-AB8B-DFB085A70341@microsoft.com...
> >> > is there a way to automatically add a new sheet to an exisiting formula
> >> > when
> >> > you add a new sheet to the book
> >> >
> >>
> >>
> >>
>
>
>

[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.....................

"Jcarriere" <Jcarriere@discussions.microsoft.com> wrote in message
news:674022EB-5956-42F0-9B2E-BF4554801A99@microsoft.com...
> Thank you now here is a dumb question how do i hide those two tabs and
> just
> those two
> Jason
>
> "Ken Wright" wrote:
>
>> 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" <Jcarriere@discussions.microsoft.com> wrote in message
>> news:2CECD12D-074F-4D49-8E32-17CD904A7D8D@microsoft.com...
>> >
>> > 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" wrote:
>> >
>> >> 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" <Jcarriere@discussions.microsoft.com> wrote in message
>> >> news:D1236054-957F-480A-AB8B-DFB085A70341@microsoft.com...
>> >> > is there a way to automatically add a new sheet to an exisiting
>> >> > formula
>> >> > when
>> >> > you add a new sheet to the book
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>