SUM function across multiple worksheets within one workbook

[John Reimer]

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!

 

[JP]

Should be

=SUM(Master:'Week 1'!Q2)


HTH,
JP

 

[Dave Peterson]

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.

 

[John Reimer]

Thanks JP, if(HTH)=yes that did the trick...
--
John Reimer

Should be

=SUM(Master:'Week 1'!Q2)


HTH,
JP

 

[John Reimer]

Great idea Dave, consider it adopted.