| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell Capture Multiple WorkSheets? | Excel Discussion (Misc queries) | |||
| Same Cell Name on Multiple Worksheets | Excel Discussion (Misc queries) | |||
| sum one cell from multiple worksheets | Excel Worksheet Functions | |||
| 1 cell average across multiple worksheets | Excel Worksheet Functions | |||
| how do I sum the same cell from multiple worksheets? | Excel Worksheet Functions | |||
Linear Mode
