Formula's won't auto calculate (Excel 2003)

Big_Mitch

New Member
Joined
Jun 28, 2011
Messages
15
Hi All

I have created a spreadsheet in Excel 2003, that has a number of countif formulas linked to another worksheet in the same workbook and up until today they have been working perfectly when the data on the workbook changes.

This morning I pasted a new months worth of data into the workbook and the formulas on the other pages will not update. If I go into the formula and hit enter the formula works. I have checked the Options and I do have the Auto Calc on. When I press F9 nothing changes. It is only when I go into the formula and hit enter that it changes.

What on earth is going on here????:confused:
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi there,

Ensure that the pasting of the data has not created any circular references (you'll see "Circular" in the status bar if there is) in the workbook as these can freeze the calculation engine and stop formulas from calculating.

Robert
 
Upvote 0
Nope, not it???? The data I paste in the worksheet is just a dump from our HR system and does not contain any formulas to create a Circular error.

also if there was a Circular then the formula would fail once I manually entered it, wouldn't it?
 
Upvote 0
OK, so circular references can be ruled out.

Is there any piece of code that swiches the calculation method to manual but not back to auto?
 
Upvote 0
Not as far as I am aware. There are only two macros in the sheet. 1 adds a column and copies a formula into it and the other changes centre code numbers to centre names. I must also admit that there is a large number of formulas (including a few arrays) in this workbook (646/sheet and there are 25 sheets).

extra item. I add a row to a sheet and all the formulas 'refreshed' themselves.
 
Upvote 0
If there's no circular references and no code that's affecting the calculation, it's either that the workbook is just so large it's very slow to calculate and/or there's a setting the in the Calculation tab from the Options dialog (Tools > Options) that's causing the issue.

What settings have been selected from the calculation tab?
 
Upvote 0
the calculation tab is set to Automatic. I have tried putting it on manual then saving/exiting the workbook, re-entering it and changing it back but still no dice.
 
Upvote 0
Also ensure the interations check box from the calculation tab is not ticked.

Besides all that, I can't think of any other possibility :confused:
 
Upvote 0

Forum statistics

Threads
1,223,521
Messages
6,172,813
Members
452,481
Latest member
Najwan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top