workbook question

J

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
 
L

Leo Heuser

Jackie said:
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 to expand...


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")
 
V

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 Noo
 
J

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!
 
R

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

Leo Heuser

Thanks, RD!

Regards
Leo Heuser


Ragdyer said:
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.
Click to expand...
 
R

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