I've searched the Excel help topics up and down but can't find a concise
answer to the following...
While working in the same workbook, what is the formula that i need to enter
in a particular cell in one worksheet that will give me the sum of other
cells in different worksheets?
For example, in Sheet 1, i want cell A1 to add up cell B52 from Sheet 2,
cell B52 from Sheet 3, etc...
anyone?

=sum(sheet2!b52+sheet3!b52) etc.

Note the exclamation point between the sheet name and the cell reference.
--
Brevity is the soul of wit.


"desperate measures" wrote:

I've searched the Excel help topics up and down but can't find a concise
answer to the following...
While working in the same workbook, what is the formula that i need to enter
in a particular cell in one worksheet that will give me the sum of other
cells in different worksheets?
For example, in Sheet 1, i want cell A1 to add up cell B52 from Sheet 2,
cell B52 from Sheet 3, etc...
anyone?

ok
i tried that, but its not quite working... it may have to do with the fact
that my sheets have been renamed? they dont actually say Sheet 1, Sheet 2,
etc...
i tried entering your formula with the actual names of the sheets but it
keeps giving me a reference error.
Then it tells me that i should enclose the names of the sheets in single
quotation marks (i.e., =sum('sheet1'!b52+'sheet2'!b52), etc... but it still
gives me a reference error.
Also, i should mention that the names of my sheets have spaces in them,
particularly like this: Tally 1, Tally 2, etc... other sheets are named
NonRepairable 1, Repairable 1, etc...
help??

=sum('tally 1'!b52,'non repairable 1'!x99)

Since you're using =sum(), you don't need the +'s inside the parens.

desperate measures wrote:

ok
i tried that, but its not quite working... it may have to do with the fact
that my sheets have been renamed? they dont actually say Sheet 1, Sheet 2,
etc...
i tried entering your formula with the actual names of the sheets but it
keeps giving me a reference error.
Then it tells me that i should enclose the names of the sheets in single
quotation marks (i.e., =sum('sheet1'!b52+'sheet2'!b52), etc... but it still
gives me a reference error.
Also, i should mention that the names of my sheets have spaces in them,
particularly like this: Tally 1, Tally 2, etc... other sheets are named
NonRepairable 1, Repairable 1, etc...
help??

--

Dave Peterson

thank you! thank you! it worked

If your sheets have unique names, insert a new dummy sheet at beginning. Name
it Start.

Insert another dummy sheet at end. Name it End.

=SUM(Start:End!B52) will cover all sheets in between no matter what the name or
spaces in those names.


Gord Dibben MS Excel MVP

On Wed, 23 Aug 2006 13:58:02 -0700, desperate measures
wrote:

ok
i tried that, but its not quite working... it may have to do with the fact
that my sheets have been renamed? they dont actually say Sheet 1, Sheet 2,
etc...
i tried entering your formula with the actual names of the sheets but it
keeps giving me a reference error.
Then it tells me that i should enclose the names of the sheets in single
quotation marks (i.e., =sum('sheet1'!b52+'sheet2'!b52), etc... but it still
gives me a reference error.
Also, i should mention that the names of my sheets have spaces in them,
particularly like this: Tally 1, Tally 2, etc... other sheets are named
NonRepairable 1, Repairable 1, etc...
help??

Gord Dibben MS Excel MVP