workbook question

[Jackie]

Hi!
If have a workbook with 4 sheets. 1st sheet named total and each sheet
after 1..2..3... I have an amount in each worksheet 1,2,3 in cell a1 that I
want to add together in a cell on the total sheet
I know that you could put in =sum(1!a1+2!a1+3!a1) in a cell on the total
sheet and you would get the totals for all three sheets ... but here's the
question - is there anyway you could right the formula to pick up any future
sheets that you would add to the workbook without having to change the
formula on the total sheet? kind of like an infinity type of thing? -- as
always you help is greatly appreciated!!!
Jackie

[raypayette]

Click on Data, Consolidate...

[VBA Noob]

=SUM(Sheet1:Sheet3!A1)

So if you had two sheets change Sheet 3 to Sheet 5

or this array (ctrl + shift + enter)

=SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&"A1")))

VBA Noob

[Leo Heuser]

"Jackie" <[email protected]> skrev i en meddelelse
news:[email protected]...
> Hi!
> If have a workbook with 4 sheets. 1st sheet named total and each sheet
> after 1..2..3... I have an amount in each worksheet 1,2,3 in cell a1 that
> I
> want to add together in a cell on the total sheet
> I know that you could put in =sum(1!a1+2!a1+3!a1) in a cell on the total
> sheet and you would get the totals for all three sheets ... but here's the
> question - is there anyway you could right the formula to pick up any
> future
> sheets that you would add to the workbook without having to change the
> formula on the total sheet? kind of like an infinity type of thing? --
> as
> always you help is greatly appreciated!!!
> Jackie


Hi Jackie

One way:

1. Insert a dummy sheet (not to contain data) to the
extreme right.
2. Name it e.g. X
3. In a cell in sheet "Total": =SUM(1:X!A1)
4. When you insert a new sheet, insert it between
sheet("1") and sheet("X")



--
Best regards
Leo Heuser

Followup to newsgroup only please.

[Jackie]

thank you so much for your help -- but I was wondering if you could tell me
what I am doing wrong. I put in the formula
=sum(n(indirect("sheet"&{1,2,3}&"!"&"A1"))) and saved it cntrl shift enter to
make it an array. it works -- but only adds the sheets 1,2,3 ... what
whould I do to have it add any additional sheets automatically? -- I don't
want to have to go into the formula and add 4,5,etc. each time I add a sheet
to the workbook... thank again!
--
Jackie


"VBA Noob" wrote:

>
> =SUM(Sheet1:Sheet3!A1)
>
> So if you had two sheets change Sheet 3 to Sheet 5
>
> or this array (ctrl + shift + enter)
>
> =SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&"A1")))
>
> VBA Noob
>
>
> --
> VBA Noob
> ------------------------------------------------------------------------
> VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833
> View this thread: http://www.excelforum.com/showthread...hreadid=570976
>
>

[VBA Noob]

Sorry

Yes you would

VBA Noob

[Leo Heuser]

Didn't my answer reach your newsserver?

Leo heuser

"Jackie" <[email protected]> skrev i en meddelelse
news:[email protected]...
> thank you so much for your help -- but I was wondering if you could tell
> me
> what I am doing wrong. I put in the formula
> =sum(n(indirect("sheet"&{1,2,3}&"!"&"A1"))) and saved it cntrl shift enter
> to
> make it an array. it works -- but only adds the sheets 1,2,3 ... what
> whould I do to have it add any additional sheets automatically? -- I don't
> want to have to go into the formula and add 4,5,etc. each time I add a
> sheet
> to the workbook... thank again!
> --
> Jackie
>
>
> "VBA Noob" wrote:
>
>>
>> =SUM(Sheet1:Sheet3!A1)
>>
>> So if you had two sheets change Sheet 3 to Sheet 5
>>
>> or this array (ctrl + shift + enter)
>>
>> =SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&"A1")))
>>
>> VBA Noob
>>
>>
>> --
>> VBA Noob
>> ------------------------------------------------------------------------
>> VBA Noob's Profile:
>> http://www.excelforum.com/member.php...o&userid=33833
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=570976
>>
>>

[Ragdyer]

That very well might be the case Leo.
It's been happening quite often in the past couple of weeks.

On several occasions, I've not been able to find my responses in the groups,
but had to go to Google to see them.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Leo Heuser" <[email protected]> wrote in message
news:[email protected]...
> Didn't my answer reach your newsserver?
>
> Leo heuser
>
> "Jackie" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > thank you so much for your help -- but I was wondering if you could tell
> > me
> > what I am doing wrong. I put in the formula
> > =sum(n(indirect("sheet"&{1,2,3}&"!"&"A1"))) and saved it cntrl shift
enter
> > to
> > make it an array. it works -- but only adds the sheets 1,2,3 ... what
> > whould I do to have it add any additional sheets automatically? -- I
don't
> > want to have to go into the formula and add 4,5,etc. each time I add a
> > sheet
> > to the workbook... thank again!
> > --
> > Jackie
> >
> >
> > "VBA Noob" wrote:
> >
> >>
> >> =SUM(Sheet1:Sheet3!A1)
> >>
> >> So if you had two sheets change Sheet 3 to Sheet 5
> >>
> >> or this array (ctrl + shift + enter)
> >>
> >> =SUM(N(INDIRECT("Sheet"&{1,2,3}&"!"&"A1")))
> >>
> >> VBA Noob
> >>
> >>
> >> --
> >> VBA Noob
>
>> ------------------------------------------------------------------------
> >> VBA Noob's Profile:
> >> http://www.excelforum.com/member.php...o&userid=33833
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=570976
> >>
> >>
>
>

[Leo Heuser]

Thanks, RD!

Regards
Leo Heuser


"Ragdyer" <[email protected]> skrev i en meddelelse
news:[email protected]...
> That very well might be the case Leo.
> It's been happening quite often in the past couple of weeks.
>
> On several occasions, I've not been able to find my responses in the
> groups,
> but had to go to Google to see them.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Leo Heuser" <[email protected]> wrote in message
> news:[email protected]...
>> Didn't my answer reach your newsserver?
>>
>> Leo heuser

[Ragdyer]

Forgot to mention that in the groups, I *could* see the OP's answer to me,
signifying that he *could see* my post.

Which I would guess means that the MS servers are not synchronizing
correctly among themselves.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Leo Heuser" <[email protected]> wrote in message
news:[email protected]...
> Thanks, RD!
>
> Regards
> Leo Heuser
>
>
> "Ragdyer" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > That very well might be the case Leo.
> > It's been happening quite often in the past couple of weeks.
> >
> > On several occasions, I've not been able to find my responses in the
> > groups,
> > but had to go to Google to see them.
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Leo Heuser" <[email protected]> wrote in message
> > news:[email protected]...
> >> Didn't my answer reach your newsserver?
> >>
> >> Leo heuser
>
>