| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUM function across multiple worksheets within one workbook
I'm having difficulty getting a sum of the contents of a cell that appears in
multiple worksheets in my workbook. It returns a VALUE error. It is: =SUM(Master!Q2:'Week 1'!Q2) where Master and Week 1 are the sheet names (with a Week 2 in between them). I've also tried it with worksheets without an intervening one (i.e. summing Q2 in weeks 1 and 2 worksheets), with the same VALUE error. Am I doing it incorrectly, or does SUM not work across worksheets the same way it does within a worksheet, and then, if so, what other alternative do I have? I know I can just use the =A+B+etc. function, but I don't want to have to edit the workbook's summary function every time an intervening worksheet is added. Thanks! -- John Reimer |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM function across multiple worksheets within one workbook
Should be
=SUM(Master:'Week 1'!Q2) HTH, JP On Jan 23, 12:16*pm, John Reimer wrote: I'm having difficulty getting a sum of the contents of a cell that appears in multiple worksheets in my workbook. *It returns a VALUE error. *It is: * * *=SUM(Master!Q2:'Week 1'!Q2) where Master and Week 1 are the sheet names (with a Week 2 in between them). *I've also tried it with worksheets without an intervening one (i.e. summing Q2 in weeks 1 and 2 worksheets), with the same VALUE error. * Am I doing it incorrectly, or does SUM not work across worksheets the same way it does within a worksheet, and then, if so, what other alternative do I have? * I know I can just use the =A+B+etc. function, but I don't want to have to edit the workbook's summary function every time an intervening worksheet is added. Thanks! -- John Reimer |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM function across multiple worksheets within one workbook
Or
=SUM('Master:Week 1'!Q2) ??? To the OP. I like to add two unused worksheets -- one to the right and one to the left. I'll name them Start and End Then I can use: =sum(start:end!Q2) And drag sheets in and out of this "sandwich" to play what if games. JP wrote: Should be =SUM(Master:'Week 1'!Q2) HTH, JP On Jan 23, 12:16 pm, John Reimer wrote: I'm having difficulty getting a sum of the contents of a cell that appears in multiple worksheets in my workbook. It returns a VALUE error. It is: =SUM(Master!Q2:'Week 1'!Q2) where Master and Week 1 are the sheet names (with a Week 2 in between them). I've also tried it with worksheets without an intervening one (i.e. summing Q2 in weeks 1 and 2 worksheets), with the same VALUE error. Am I doing it incorrectly, or does SUM not work across worksheets the same way it does within a worksheet, and then, if so, what other alternative do I have? I know I can just use the =A+B+etc. function, but I don't want to have to edit the workbook's summary function every time an intervening worksheet is added. Thanks! -- John Reimer -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM function across multiple worksheets within one workbook
Great idea Dave, consider it adopted. :)
-- John Reimer "Dave Peterson" wrote: Or =SUM('Master:Week 1'!Q2) ??? To the OP. I like to add two unused worksheets -- one to the right and one to the left. I'll name them Start and End Then I can use: =sum(start:end!Q2) And drag sheets in and out of this "sandwich" to play what if games. JP wrote: Should be =SUM(Master:'Week 1'!Q2) HTH, JP On Jan 23, 12:16 pm, John Reimer wrote: I'm having difficulty getting a sum of the contents of a cell that appears in multiple worksheets in my workbook. It returns a VALUE error. It is: =SUM(Master!Q2:'Week 1'!Q2) where Master and Week 1 are the sheet names (with a Week 2 in between them). I've also tried it with worksheets without an intervening one (i.e. summing Q2 in weeks 1 and 2 worksheets), with the same VALUE error. Am I doing it incorrectly, or does SUM not work across worksheets the same way it does within a worksheet, and then, if so, what other alternative do I have? I know I can just use the =A+B+etc. function, but I don't want to have to edit the workbook's summary function every time an intervening worksheet is added. Thanks! -- John Reimer -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SUM function across multiple worksheets within one workbook
Thanks JP, if(HTH)=yes :-) that did the trick...
-- John Reimer "JP" wrote: Should be =SUM(Master:'Week 1'!Q2) HTH, JP On Jan 23, 12:16 pm, John Reimer wrote: I'm having difficulty getting a sum of the contents of a cell that appears in multiple worksheets in my workbook. It returns a VALUE error. It is: =SUM(Master!Q2:'Week 1'!Q2) where Master and Week 1 are the sheet names (with a Week 2 in between them). I've also tried it with worksheets without an intervening one (i.e. summing Q2 in weeks 1 and 2 worksheets), with the same VALUE error. Am I doing it incorrectly, or does SUM not work across worksheets the same way it does within a worksheet, and then, if so, what other alternative do I have? I know I can just use the =A+B+etc. function, but I don't want to have to edit the workbook's summary function every time an intervening worksheet is added. Thanks! -- John Reimer |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| opening multiple files into one workbook, but multiple worksheets.... | Excel Discussion (Misc queries) | |||
| Open multiple files into multiple worksheets of the same workbook | Excel Discussion (Misc queries) | |||
| Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
| Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) | |||
| Multiple Worksheets in a workbook | Excel Worksheet Functions | |||
Hybrid Mode
