If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below. If you don't like Google AdSense in the posts, register or log in above.

Sum Same Cell Across Multiple Worksheets

Hello all,

I have about 100 worksheets in my excel workbook and each sheet is identical. I would like to create a control sheet that would add the cells of the different worksheets together and place it in the control. I tried doing it manually but found out quickly that it would take forever since I have a about 10 cells that I need to put on the control sheet.

For example on the control sheet in J11 I want to put in: ='1'!J11+'2'!J11......'100'!J11

Re: Sum Same Cell Across Multiple Worksheets

Re: Sum Same Cell Across Multiple Worksheets

I have tried all of the recommendations and they all work - except, when I try to copy the formula to another cell to get the value from the sum of those cells in the same collection of spreadsheets it doesn't work? When I view the formula it appears to have updated the cell reference, but the cell value is the same as the first cell I worked on. When I manually enter the formula (sum(sheet1:sheet3!a1) in cell a2 (Sum(Sheet1:sheet3!a2) it works? Why would it not update the value when I copy from a1 and paste in a2 but work when I manually enter the formula with the same syntax??

Re: Sum Same Cell Across Multiple Worksheets

Hi All, Great info and solution to the original question. Instead of defining the tab range with '*' or Sheet1:Sheet3 how could I use a list of tab names? Therefore, if I create a list of tab names the Sum function would know to add the cell reference from each of the tabs listed.