calculation failure when summing across sheets

[Craig_Richards]

I am summing the same cell across a number of sheets and deducting a further total:

=SUM('TGN:TOA'!D12)-'TMG'!D12

The sheets TGN to TOA are all next to each other (ie I don't have other sheets in between) and sheet TMG is located at the start of the workbook.

It seems that the calculation is not updating automatically changes in the sheets included within the sum range. Calculation is set to automatic but I need to enter each cell (with F2) and then press enter in order to get the totals to update. This is not ideal when I have 300 cells to update!

Any idea what the problem might be?

[dominicb]

Good evening Craig

I think that this is nothing more sinister than a slight syntax hiccup!

Try using your formula like this:

=SUM(TGN:TOA!D12)-TMG!D12

and that should be OK.

HTH

DominicB

[Craig_Richards]

Hello again dominic,

Syntax is not my problem unfortunately (it wirks if I refresh each cell individually). I tried playing around with calculation iterations but to no avail.

Any ideas still?

[dominicb]

Hi Craig

I copied your formula into a blank sheet and renamed some of the worksheet names to match yours and got an error - I changed the formula syntax slightly and it worked. Problem solved. But not, it seems.

My immediate thought was the calculation setting being set incorrectly, but you seem to have that angle covered, so I have to admit I don't know what to do next...

Try posting your original request again and see if anyone else has any bright ideas.

Sorry I can't be of any further help...

DominicB

[Craig_Richards]

One of those mysterious little excel bugs I guess. I'll have to use the old cell + cell + cell

Thanks