failure to auromatically calculate

[denisaf]

A fault has recently developed on one sheet of an EXCEL 2000 workbook. New values are now not automatically calculated when I input a new value. I have to press F9 repeatedly to get all values calculated. Options>Calculation>Automatic is the setting but it does not work. Auditing does not indicate any problem cell.

 

[saltkev]

Hi

I have not come across this before, but it might be worth checking and code if it there is any being run for lines like these.

"Application.Calculation = xlManual"

"Application.Calculation = xlAutomatic"

These lines switch off and on the auto-calc functionality.

Hope it helps

SaltKev

 

[denisaf]

I do not understand what the lines are that you quoted. My sheet contains labels, formulae and values in the cells. The problem arose after some alterations in formulae but I did not change any of the Options. Calculations are set at Automatic. Thanks for the reply.

 

[saltkev]

OK My mistake should have explained, these snippets of code can be written using VBA, behind the scenes in your sheet, they automatically switch on and off the auto calc functionality. clearly from your last message, it seems to be the result of some other changes that were made.

regards


Saltkev

 

[denisaf]

I have now reinstalled Office 2000 (so Excel) and done Detect and Repair to no avail. The Options>Calculate is on Automatic but I still have to press F9 a number of times to get a correct answer for each value input.

 

[Burrgogi]

I have now reinstalled Office 2000 (so Excel) and done Detect and Repair to no avail. The Options>Calculate is on Automatic but I still have to press F9 a number of times to get a correct answer for each value input.

Denisaf,

Have you considered that maybe the problem lies with your worksheet or workbook. Instead of re-installing Office, I would have created a new worksheet and tested some sample data to be calculated.

2 things to try. In the cell where you want the total to appear, click on that cell then press F2. If this is an autosum, look at the cells in your formula and check to make sure that it's referencing the correct cells.

Since you haven't posted any particular details about your worksheet setup, (or the formula) you could try copying the worksheet in question to a new workbook and see if the proper calculation takes place or not. If it does, then you know the problem most likely lies with your worksheet.

 

[denisaf]

Burrgogi
I have copied the faulty sheet to a new work book but the problem remains.
I have checked calculations in another sheet of the first workbook and they worked.
Some of the sets of calculations in the faulty sheet work while others do not.
I am checking for a link between those that do not work by using the Auditing tool.

 

[denisaf]

I now know that the faulty sections involve hundred of calculations. I can get these calculations completed by pressing F9 repeatedly. Other smaller sections of calculations are carried out automatically. I understand there are limits to the number of automatic calculations but I cannot find what these limits are and how to change them.