Summing across multiple worksheets in the same workbook

[JSEMP]

Hi

I am pulling my hair out with this. I am sure there is a simple answer.

I am trying to sum cell AM4 across multiple worksheets labelled months APRIL-MARCH (financial year).

The Cell AM4 in each worksheet currently has the following formula =SUM(AD4,U4,L4,C4) (yes i selected the cells backwards and could have just used cell+cell+cell).

The totals worksheet I have created is to, as the name suggest, total the other worksheets.

The formula I am using to sum the cell AM4 across the 12 monthly worksheets is =SUM(APRIL:MAR!AM4)

The resulting value in that box is 0, despite every worksheet summing correctly and having its own value.

What am I doing wrong?

 

[Aladin Akyurek]

Create tw0 new sheets, name them First and Last, and place all of the relevant sheets APRIL etc. between these two. Now invoke:

=SUM(First:Last!AM4)

If you still get 0, what do you get with:

=APRIL!AM4

etc.?

 

[energman58]

Dont think you are doing anything wrong but two suggestions to try:

Check what =April!AM4 gets you

Insert two new worksheets one named first before your April sheet and one named last after your March sheet and try this formaula =Sum(First:Last!AM4) (you can hide First and Last sheets)

If that does not work I am baffled

 

[JSEMP]

Dont think you are doing anything wrong but two suggestions to try:

Check what =April!AM4 gets you

Insert two new worksheets one named first before your April sheet and one named last after your March sheet and try this formaula =Sum(First:Last!AM4) (you can hide First and Last sheets)

If that does not work I am baffled

thanks to you both

=APRIL!AM4 gets me the correct figure that is in sheet APRIL and cell AM4

now inserted two worksheets named First before April and last after march. did the above suggestion and its decided to go back to 0

.......confusing

 

[energman58]

And it really is zero? try refering it in a simple formula and see if it stays 0

 

[JSEMP]

Sorry what do you mean a simple formula?

 

[energman58]

If your annswer is in say B1 put =B1*1 in an adjacent cell and see if that gives anything other than zero. I am confused because what you are doing seems right so something strange is happening somewhere

 

[JSEMP]

If your annswer is in say B1 put =B1*1 in an adjacent cell and see if that gives anything other than zero. I am confused because what you are doing seems right so something strange is happening somewhere

New development

error pops up when i click 'calculate sheet' saying
' careful we found one or more circular references in your workbook that might cause your formulas to calculate incorrectly'
so i checked all the formulas in all the box related to cell AM4 in each spreadsheet and could not find anything

it also shows the correct figure after I click okay

 

[energman58]

I assume that you already checked that there is actually something in AM4 and you have not gained or lost a column somewhere that is pointing your formula at the wrong place

 

[JSEMP]

Final development I have fixed it. Somehow checking all 60 cells over and over I kept missing 1 cell in particular relating to the final formula..............sorry all but thanks for the help!!!!

by the way the cell in question was just meant to be a figure but was instead teh formula i was trying to put in my totals worksheet. So that was the circular part mentioned.