Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
=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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indirect cell reference using copies of worksheets in same workboo | Excel Worksheet Functions | |||
Use the same cell from many worksheets to create a chart? | Charts and Charting in Excel | |||
Cell to equal a worksheet's name | Excel Discussion (Misc queries) | |||
Grand total same cell in all worksheets | Excel Worksheet Functions | |||
how do I sum the same cell from multiple worksheets? | Excel Worksheet Functions |