Sum across mutiple worksheets displaying incorrect result

[tkday]

Hi All,


I've been searching on google for a few hours and have been unable to come up with an answer. I have a spreadsheet that has multiple worksheets inside and I am trying to get a sum of a certain cell across all of them. The computer is running windows 7 64-bit with office 2007 but I have also tried opening it up with Office 2010. This is the forumla I am using:

=SUM(Sheet1:Sheet20!C34)


In this particular cell (c34) on all sheets the value is 0 and it is just a general format cell. It's coming back with a figure of 20 when adding them all up. However if I just do a sum of the first 15 (sheet1:sheet15!c34) it comes back as 0 like it should. The minute I include an extra sheet, doesn't matter which one after 15 it returns a result of 20. I've dumbed down the naming and the actual amount of sheets I have to make it easier to explain the but the concept is the same. I have tried a few different things like moving one of the sheets before sheet 15 and then it returns 20, i've tried moving multiple sheets before sheet 15 and the result is still 20. I've tried just moving one random one, eg sheet 19 and the result is always 20. I decided that maybe there is a limit so I started at sheet 4 and finished at sheet 16 and it still displays a result of 20.

I decided to do a trace precedents with the formular =SUM(Sheet1:Sheet16!C34) making sure to include one fault sheet only and then go do a trace on that last sheet and it takes me to the cell that it's getting the sum from and it's the correct cell with a general format and the vaule of 0. I have tried deleting that row and sticking in a new one which is still at c34 with nothing in it at all and it still results in the number 20. I can't see any hidden rows or anything.

Any help would be appreciated but keeping in mind I am no Excel expert


Thanks.

[:) Sixthsense :)]

There should be a hidden sheet between Sheet1 to Sheet20 which possess that value.

[tkday]

Thanks for the reply. That is exactly what I though, so that is why I started narrowing it down. I narrowed down to 1-15 displaying the correct result. As soon as I move any of the other sheets in between the 1-15 for example 19 it will display the result of 20. Doesn't matter which sheet I move in. If I change the vaule from 0 to 1 on the sheet the result will be 21. So I know it's calculating it properly. Hopefully that makes sense.

[:) Sixthsense :)]

Add a volatile function which will recalculate on any change

=SUM(Sheet1:Sheet5!A1)+(TODAY()-TODAY())

[tkday]

Not sure what that was supposed to do but after changing the formula the result still ended up being 20. Then I did the same thing as before. Changed formular to only include sheets1-15 and then moved 16-20 individually between 1-15 so it included each one and this time the result was still 0. So now the sheets were arranged 1-14 16 17 18 19 20 15 and the result was still 0. I then moved 15 back into the correct position so it was 1-20 and the result was still 0. I then removed the +(TODAY..... from the end of the formula changed formula back to the original sheet1-sheet20 and the result still 0. It's now displaying it correctly and I have the orginally formula and sheets the same way as they were before but it's working now. Doesn't make sense to me.

[tkday]

Also would just like to say that there were other fields in the spreadsheet that didn't work. I only ever put the above formula in one cell but it fixed all of them for some reason. Thanks for your help sixthsense, I have no idea why it fixed it but it did so that's all that matters

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.