Sum cell value across multiple worksheets.
 

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

how do you hide a worksheeet? I can not figure it out. Is it right in front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

It sure is. Right click the sheets tab...
Micky


"Damian" wrote:

how do you hide a worksheeet? I can not figure it out. Is it right in front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

Hide: Select the sheet, then FormatSheetHide

If you setup the sheets at the start and end and then hide them, and new
sheets will always be between them.

HTH

Bob

"Damian" wrote in message
...
how do you hide a worksheeet? I can not figure it out. Is it right in
front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet
called
'Start' at the front fo the book an done called 'End' at the end of the
book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be
added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this
function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50
or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if
you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if
you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

Pre XL2007 Format | Sheet Hide
Xl2007 right click the sheet tab...

So long as you are adding up ALL sheets this is relatively foolproof. Any
worksheets inserted will be after the start sheet but before the end sheet.
--
HTH...

Jim Thomlinson


"Damian" wrote:

how do you hide a worksheeet? I can not figure it out. Is it right in front
of me?

also.

how would I make sure any other sheet is added between start/end?

thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

To hide a worksheet Format Sheet Hide.
To unhide do the same and select Unhide.

When you hide the Start and End any sheets you insert will be between them.

HTH
Regards,
Howard

"Damian" wrote in message
...
Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if
you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

=IF(C2=1,SUM(First:Last!A14),"")

HTH
Peter

"Damian" wrote:

Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

Perfect. Thank you so much.

"Billy Liddel" wrote:

=IF(C2=1,SUM(First:Last!A14),"")

HTH
Peter

"Damian" wrote:

Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you

Sum cell value across multiple worksheets.
 

Hi, I thought this would do the trick, but it did not because I stated it
wrong.

What i would like to do is:
ex. I have sheet 1 through sheet 5 (plus the hidden First:Last)
In some of these sheets in cell "C2" I would like to put eather 1 and have a
function that woud sum it up.

so If I put 1 in sheet1 and sheet3 the function should add only these two.

something like this(I know it is wrong, but gives an idea)
=IF((First:Last!C2=1),SUM(First:Last!A14),"")

is this possible?

Thank you.

"Billy Liddel" wrote:

=IF(C2=1,SUM(First:Last!A14),"")

HTH
Peter

"Damian" wrote:

Also. Can I put a condition in front of a formula? or do I have to use a micro?

ex: I want.

If cell "A2" is 1 then include is sum, if it is 2 then do not include?

Thank you

"Jim Thomlinson" wrote:

My solution to that is to add in place holder sheets. Add a newe sheet called
'Start' at the front fo the book an done called 'End' at the end of the book.
Now make your formula
=sum(Start:End!A1)
Now hide the Start and End sheets. Any sheets that are added will be added
between start and end so the total will include all sheets.
--
HTH...

Jim Thomlinson


"Damian" wrote:

Do sum up cell A1 of each worksheet from 1 to 6 I would use this function:
=SUM(Sheet1:Sheet6!A1)

BUT what if I don't know how many worksheets I will be having. Maybe 50 or
100.
How can I write a function to sum up all the worksheets?

When I use this formula:
=SUM('*'!A1) It works if you know how many you will have, but not if you
keep adding more. Because that function above turns into this
=SUM(Sheet1:Sheet6!A1) - depending how many worsheets you have, so if you
would add a 7th, it would not count that.

Anyone have any suggestions?

Thank you