| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sum across multiple worksheets
My problem has a simple description but apparently not a simple solution.
I have a worksheet, call it 'Summary', where I want each cell in column B to be set to the sum of the cell in the same position in all worksheets named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 = S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ... The number of S<digit sheets will change from time to time (although they will always be contiguous starting at 1). I have these requirements: 1) I do not want to have to change the content of the cells in Summary column B whenever that happens. (I may be doing the same thing with additional columns.) 2) I will add and subtract rows from column B periodically and do not want to have to edit the formulas in the summary column when that happens. I want to be able to just copy and paste the formula into a new cell when expanding the length of the column. I can put the names of the S<digit worksheets in cells or a name and change that whenever the worksheets change. This has to work on Excel V.X for Mac in addition to Office 2003 for PC. If you know the answer you can stop here; the rest is just to prove I have tried :-) I have a long list of things that have not worked. Obviously =SUM(S1:S8!B1) satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results: =SUM(INDIRECT(X1)&"!B1")) ... #VALUE {=SUM(INDIRECT(X1)&"!B1"))} ... #REF {=SUM(SUMIF(INDIRECT(X1)&"!B1"),"0")} ... #REF Putting 'S1', 'S2', ... 'S8' into X1:X8 and using {=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),"0")} works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting rows.) For some reason I need SUMIF to get anywhere because if I try {=SUM(INDIRECT(X1:X8)&"!B1")} I get only the value of S1!B1. "0" is an acceptable condition. Now, {=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),"0")} works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge against all future increases I get #REF when there is nothing in X9. {=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),"0"))} where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1. Defining SheetRange as 'S1:S8' and trying =SUM(SheetRange!B1) gives #REF. I even thought I could insert dummy worksheets that would go at the beginning and end, sum across all worksheets but test using CELL to test the worksheet of each cell to see if it matches S<digit, but CELL doesn't seem to work with 3D references. I've attempted some things with double INDIRECTs that are too embarrassing to post here. Help...! -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum across multiple worksheets
Maby this can point u in right direction:
Say u got summary,S1,S2,S3,S4,S5,S6,S7,S8 (9 Sheets) now add 2 more sheets named first and last and arrange them : summart,first,S1,S2,S3,S4,S5,S6,S7,S8,last Now hide sheet(first) and sheet(last) Then u can use SUM(first:last!B1) "Peter Scott" skrev: My problem has a simple description but apparently not a simple solution. I have a worksheet, call it 'Summary', where I want each cell in column B to be set to the sum of the cell in the same position in all worksheets named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 = S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ... The number of S<digit sheets will change from time to time (although they will always be contiguous starting at 1). I have these requirements: 1) I do not want to have to change the content of the cells in Summary column B whenever that happens. (I may be doing the same thing with additional columns.) 2) I will add and subtract rows from column B periodically and do not want to have to edit the formulas in the summary column when that happens. I want to be able to just copy and paste the formula into a new cell when expanding the length of the column. I can put the names of the S<digit worksheets in cells or a name and change that whenever the worksheets change. This has to work on Excel V.X for Mac in addition to Office 2003 for PC. If you know the answer you can stop here; the rest is just to prove I have tried :-) I have a long list of things that have not worked. Obviously =SUM(S1:S8!B1) satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results: =SUM(INDIRECT(X1)&"!B1")) ... #VALUE {=SUM(INDIRECT(X1)&"!B1"))} ... #REF {=SUM(SUMIF(INDIRECT(X1)&"!B1"),"0")} ... #REF Putting 'S1', 'S2', ... 'S8' into X1:X8 and using {=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),"0")} works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting rows.) For some reason I need SUMIF to get anywhere because if I try {=SUM(INDIRECT(X1:X8)&"!B1")} I get only the value of S1!B1. "0" is an acceptable condition. Now, {=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),"0")} works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge against all future increases I get #REF when there is nothing in X9. {=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),"0"))} where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1. Defining SheetRange as 'S1:S8' and trying =SUM(SheetRange!B1) gives #REF. I even thought I could insert dummy worksheets that would go at the beginning and end, sum across all worksheets but test using CELL to test the worksheet of each cell to see if it matches S<digit, but CELL doesn't seem to work with 3D references. I've attempted some things with double INDIRECTs that are too embarrassing to post here. Help...! -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum across multiple worksheets
=SUM(S1:S8!B1)
=SUM(INDIRECT(X1)&"!B1")) Think INDIRECT doesn't work with 3d references. An alternative way In your summary sheet, List the 8 sheetnames across in X1:AE1, ie: S1, S2, ... S8 Place in X2: =INDIRECT("'"&X1&"'!B1") Copy across to AE2 Then you could use this in say B2: =SUMIF(X2:AE2,"<#REF!") Extend to suit -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Peter Scott" wrote in message ... My problem has a simple description but apparently not a simple solution. I have a worksheet, call it 'Summary', where I want each cell in column B to be set to the sum of the cell in the same position in all worksheets named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 = S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ... The number of S<digit sheets will change from time to time (although they will always be contiguous starting at 1). I have these requirements: 1) I do not want to have to change the content of the cells in Summary column B whenever that happens. (I may be doing the same thing with additional columns.) 2) I will add and subtract rows from column B periodically and do not want to have to edit the formulas in the summary column when that happens. I want to be able to just copy and paste the formula into a new cell when expanding the length of the column. I can put the names of the S<digit worksheets in cells or a name and change that whenever the worksheets change. This has to work on Excel V.X for Mac in addition to Office 2003 for PC. If you know the answer you can stop here; the rest is just to prove I have tried :-) I have a long list of things that have not worked. Obviously =SUM(S1:S8!B1) satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results: ... #VALUE {=SUM(INDIRECT(X1)&"!B1"))} ... #REF {=SUM(SUMIF(INDIRECT(X1)&"!B1"),"0")} ... #REF Putting 'S1', 'S2', ... 'S8' into X1:X8 and using {=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),"0")} works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting rows.) For some reason I need SUMIF to get anywhere because if I try {=SUM(INDIRECT(X1:X8)&"!B1")} I get only the value of S1!B1. "0" is an acceptable condition. Now, {=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),"0")} works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge against all future increases I get #REF when there is nothing in X9. {=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),"0"))} where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1. Defining SheetRange as 'S1:S8' and trying =SUM(SheetRange!B1) gives #REF. I even thought I could insert dummy worksheets that would go at the beginning and end, sum across all worksheets but test using CELL to test the worksheet of each cell to see if it matches S<digit, but CELL doesn't seem to work with 3D references. I've attempted some things with double INDIRECTs that are too embarrassing to post here. Help...! -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum across multiple worksheets
No, because there are other worksheets aside from S<digit that contain
numbers in column B that I do not want summed in Summary. On Wed, 12 Dec 2007 19:50:00 -0800, excelent wrote: Maby this can point u in right direction: Say u got summary,S1,S2,S3,S4,S5,S6,S7,S8 (9 Sheets) now add 2 more sheets named first and last and arrange them : summart,first,S1,S2,S3,S4,S5,S6,S7,S8,last Now hide sheet(first) and sheet(last) Then u can use SUM(first:last!B1) "Peter Scott" skrev: My problem has a simple description but apparently not a simple solution. I have a worksheet, call it 'Summary', where I want each cell in column B to be set to the sum of the cell in the same position in all worksheets named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 = S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ... The number of S<digit sheets will change from time to time (although they will always be contiguous starting at 1). I have these requirements: 1) I do not want to have to change the content of the cells in Summary column B whenever that happens. (I may be doing the same thing with additional columns.) 2) I will add and subtract rows from column B periodically and do not want to have to edit the formulas in the summary column when that happens. I want to be able to just copy and paste the formula into a new cell when expanding the length of the column. I can put the names of the S<digit worksheets in cells or a name and change that whenever the worksheets change. This has to work on Excel V.X for Mac in addition to Office 2003 for PC. If you know the answer you can stop here; the rest is just to prove I have tried :-) I have a long list of things that have not worked. Obviously =SUM(S1:S8!B1) satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results: =SUM(INDIRECT(X1)&"!B1")) ... #VALUE {=SUM(INDIRECT(X1)&"!B1"))} ... #REF {=SUM(SUMIF(INDIRECT(X1)&"!B1"),"0")} ... #REF Putting 'S1', 'S2', ... 'S8' into X1:X8 and using {=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),"0")} works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting rows.) For some reason I need SUMIF to get anywhere because if I try {=SUM(INDIRECT(X1:X8)&"!B1")} I get only the value of S1!B1. "0" is an acceptable condition. Now, {=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),"0")} works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge against all future increases I get #REF when there is nothing in X9. {=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),"0"))} where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1. Defining SheetRange as 'S1:S8' and trying =SUM(SheetRange!B1) gives #REF. I even thought I could insert dummy worksheets that would go at the beginning and end, sum across all worksheets but test using CELL to test the worksheet of each cell to see if it matches S<digit, but CELL doesn't seem to work with 3D references. I've attempted some things with double INDIRECTs that are too embarrassing to post here. Help...! -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sum across multiple worksheets
So, put these other sheets either before the sheet that you named first, or
after the one named last. -- David Biddulph "Peter Scott" wrote in message ... No, because there are other worksheets aside from S<digit that contain numbers in column B that I do not want summed in Summary. On Wed, 12 Dec 2007 19:50:00 -0800, excelent wrote: Maby this can point u in right direction: Say u got summary,S1,S2,S3,S4,S5,S6,S7,S8 (9 Sheets) now add 2 more sheets named first and last and arrange them : summart,first,S1,S2,S3,S4,S5,S6,S7,S8,last Now hide sheet(first) and sheet(last) Then u can use SUM(first:last!B1) "Peter Scott" skrev: My problem has a simple description but apparently not a simple solution. I have a worksheet, call it 'Summary', where I want each cell in column B to be set to the sum of the cell in the same position in all worksheets named 'S1', 'S2', ... 'S8' (currently). I.e., Summary!B1 = S1!B1+S2!B1+...+S8!B1. And so forth for Summary!B2, ... The number of S<digit sheets will change from time to time (although they will always be contiguous starting at 1). I have these requirements: 1) I do not want to have to change the content of the cells in Summary column B whenever that happens. (I may be doing the same thing with additional columns.) 2) I will add and subtract rows from column B periodically and do not want to have to edit the formulas in the summary column when that happens. I want to be able to just copy and paste the formula into a new cell when expanding the length of the column. I can put the names of the S<digit worksheets in cells or a name and change that whenever the worksheets change. This has to work on Excel V.X for Mac in addition to Office 2003 for PC. If you know the answer you can stop here; the rest is just to prove I have tried :-) I have a long list of things that have not worked. Obviously =SUM(S1:S8!B1) satisfies #2 but not #1. Putting 'S1:S8' in X1 gives these results: =SUM(INDIRECT(X1)&"!B1")) ... #VALUE {=SUM(INDIRECT(X1)&"!B1"))} ... #REF {=SUM(SUMIF(INDIRECT(X1)&"!B1"),"0")} ... #REF Putting 'S1', 'S2', ... 'S8' into X1:X8 and using {=SUM(SUMIF(INDIRECT(X1:X8)&"!B1"),"0")} works but doesn't satisfy #2 ("B1" doesn't adjust when deleting or pasting rows.) For some reason I need SUMIF to get anywhere because if I try {=SUM(INDIRECT(X1:X8)&"!B1")} I get only the value of S1!B1. "0" is an acceptable condition. Now, {=SUM(SUMIF(INDIRECT($X$1:$X$8&"!B"&ROW()),"0")} works but doesn't satisfy #1, and if I try using $X$1:$X$100 to hedge against all future increases I get #REF when there is nothing in X9. {=SUM(SUMIF(INDIRECT($X$1:$X$2&"!B1"),"0"))} where X1:X2 contains {'S1';'S8'} only yields S1!B1+S8!B1. Defining SheetRange as 'S1:S8' and trying =SUM(SheetRange!B1) gives #REF. I even thought I could insert dummy worksheets that would go at the beginning and end, sum across all worksheets but test using CELL to test the worksheet of each cell to see if it matches S<digit, but CELL doesn't seem to work with 3D references. I've attempted some things with double INDIRECTs that are too embarrassing to post here. Help...! -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ -- Peter Scott http://www.perlmedic.com/ http://www.perldebugged.com/ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to make a macro to clear multiple cells from multiple worksheets? | Excel Worksheet Functions | |||
| Delete Blank Rows Code - Multiple Worksheets - Multiple Documents | Excel Discussion (Misc queries) | |||
| opening multiple files into one workbook, but multiple worksheets.... | Excel Discussion (Misc queries) | |||
| Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
| adding certain cells in multiple worksheets in multiple workbooks | Excel Worksheet Functions | |||
Linear Mode
