Formula not updating? - help please

[MrsNic]

I have created a simple spreadsheet for a colleague that adds up all the data in 3 different columns.

I am using =SUM(C2:C39,D239,F2:F39)
The total is displayed in a merged cell.

All was working well, but suddenly it won't update when the data is altered. If you double click to enter the cell that contains the formula, then click out of the cell it updates.

Can I write a macro or something to update the cell as soon as any data is changed.

It has worked perfectly well for months!!

Cath

 

[HarleyQuinn]

Try pressing F9 and see if it calculates then?

If it does you could try going to Tools-->Options-->Calculation Tab and seeing if it is set to manual. Set it back to Automatic if it is.

Hope this helps



HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[MrsNic]

Thanks for the tip, but I had tried that. Also Shift+F9 and all the other combinations. The Calculation tab is set to automatic so that is not the problem. I am really confused, I even started re-entering some of the data and it worked for about half the entries and then stopped.

Cath

 

[macropod]

Hi Cath,

Sounds like you might have a circular referencing error on your worksheet. Is there a message like "Circular: A1" on the Status Bar? If so, you'll need to correct whatever's causing the circular reference. Alternatively, if you need to have a circular reference, you'll also need to tell Excel how many and what size iterations to go through when recalculating (Tools|Options|Calculation|Iteration)

Cheers