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

Click on Data, Consolidate...


--
raypayette


------------------------------------------------------------------------
raypayette's Profile: http://www.excelforum.com/member.php...o&userid=29569
View this thread: http://www.excelforum.com/showthread...hreadid=570976

"Jackie" skrev i en meddelelse
...
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.

=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

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

Sorry

Yes you would

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

Didn't my answer reach your newsserver?

Leo heuser

"Jackie" skrev i en meddelelse
...
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

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" wrote in message
...
Didn't my answer reach your newsserver?

Leo heuser

"Jackie" skrev i en meddelelse
...
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

Thanks, RD!

Regards
Leo Heuser


"Ragdyer" skrev i en meddelelse
...
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" wrote in message
...
Didn't my answer reach your newsserver?

Leo heuser

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" wrote in message
...
Thanks, RD!

Regards
Leo Heuser


"Ragdyer" skrev i en meddelelse
...
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" wrote in message
...
Didn't my answer reach your newsserver?

Leo heuser