Sum a cell within all worksheets
 

Hi!

I have a very big workbook containing 52 identical worksheets.
Plus Summary sheet in the same workbook identical to all worksheets.
I need a formula that will summarize every single cell all over the
worksheets. Something like consolidate but within the sheets in one
workbook.

For example:
=Sheet1!A1+Sheet2!A1+Sheet3!A1

I can't use this simple formula as:
1. Too many sheets
2. Sheet names are long
3. Formula becomes very big

Is there any other function that will make it?

Thanks in advance for your help.
Irada


--
Irada Shamilova
------------------------------------------------------------------------
Irada Shamilova's Profile: http://www.excelforum.com/member.php...o&userid=12286
View this thread: http://www.excelforum.com/showthread...hreadid=381871

=SUM(Sheet1:Sheet3!A2)

or

=SUM(Sheet1:Sheet52!A2) in your case


Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381871

Exactly what I need!
Thanks!


--
Irada Shamilova
------------------------------------------------------------------------
Irada Shamilova's Profile: http://www.excelforum.com/member.php...o&userid=12286
View this thread: http://www.excelforum.com/showthread...hreadid=381871

Will this formula be valid if we rename a sheet, say sheet 20 ?
Thanks .


--
tacarme
------------------------------------------------------------------------
tacarme's Profile: http://www.excelforum.com/member.php...o&userid=24453
View this thread: http://www.excelforum.com/showthread...hreadid=381871

Yes, it should. And also if you add new sheets between the two sheets
specified in the formula.

Mangesh


--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381871

there is an excellent method shown for summarizing sheets shown on the
bygsoftware site.

http://www.bygsoftware.com/Excel/Int.../breadroll.htm

oddly called 'bread roll method'
but defiantly worth a look.

The gist is if you have the following sheet names

Summary, Bread, Area1, Area2, Area3, Roll, Area4
and you leave sheets Bread and Roll blank

If you put the formula =Sum(Bread:Roll!D3) it will return the sum of D3 in
all sheets between Bread and Roll ie. Area1-3.

You are then able to move sheets in and out from between Bread and Roll to
include or remove them from the summary.

I promise the real example is more clear.

HTH
RES

But not so, if renamed sheet 20 is moved to the end (right-most-position),
meaning outside the internal index numbering scheme initially established.
HTH

"tacarme" wrote in
message ...

Will this formula be valid if we rename a sheet, say sheet 20 ?
Thanks .


--
tacarme
------------------------------------------------------------------------
tacarme's Profile:
http://www.excelforum.com/member.php...o&userid=24453
View this thread: http://www.excelforum.com/showthread...hreadid=381871

Hi Jim,

When I tried renaming the sheet, it didn't show the behaviour you
mentioned in your post. The formula accomodated it without any
problem.

Mangesh



But not so, if renamed sheet 20 is moved to the end
(right-most-position),
meaning outside the internal index numbering scheme initially
established.
HTH



--
mangesh_yadav
------------------------------------------------------------------------
mangesh_yadav's Profile: http://www.excelforum.com/member.php...o&userid=10470
View this thread: http://www.excelforum.com/showthread...hreadid=381871