v odd formula replication!

preqin

Board Regular
Joined
Mar 15, 2005
Messages
83
Ok, apparantly this has happened a few times before (when I was not around)........

Very simple formula: H2/G2*100 which gives the result say 10, however, when this is replicated down- the results are all 10- not different results as they should be....

however- when you double click in the cell- it corrects it and makes it the right value.....

any ideas?! (This is the same on 3 machines and has been tried on 2000, 2003 and 2007!)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Preqin

Do you have Automatic calculation enabled? Tools>Options>Calculation tab and make sure Automatic Calculation is selected.

Best regards

Richard
 

preqin

Board Regular
Joined
Mar 15, 2005
Messages
83
Hi Richard,

Very odd- if i open excel (either version) and look- yes- automatic calculation is indeed ticked- and if i try a similar formula on this blank workbook it works fine.....

However, if I open the workbook which had the problems- then yes, the calculation changes to manual!!- changing to auto fixes the problem.

That seems very strange to me- something must be imbedded in the workbook that tells excel to override the current settings- strange and annoying?!!

(Btw- i should have said- I am Rob, preqin is just the username!)
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

Hi Rob :)

If Calculation was set to Manual when you last saved down the workbook, it will open up with Manual calculation applied if it is the first workbook you open within Excel (after the default Book1).

Best regards

Richard
 

patrickmuldoon99

Active Member
Joined
Jun 27, 2006
Messages
345
press Alt+F11 and goto Edit --> Find. Ensure that you are searching in the entire project, and do a search for 'manual'

If you find the line
Code:
application.calculation = xlcalculationmanual

then that is whats causing you the problem. I would say if it's anywhere its going to be in your workbook_open event. Deleting that line will stop calculation being reset to manual.

Alternatively, if the file contains a lot of links / lookups etc. then you may have exceeded Excel's dependency tree, at which time it will not calculate cells unless instructed to. You can usually tell if this is the case as Excel will display 'Calculate' in your status bar when the file is loaded up.

HTH, Patrick
 

preqin

Board Regular
Joined
Mar 15, 2005
Messages
83
Hi Patrick,

I looked for this line of code in the workbook- there was no reference to it, very quirky!

The file has no links, and is a pretty tiny file actually- 400-500 rows and about 6-8 columns

Strange, assuming it only happens once in a blue moon (as has been the case in the past) then Ill just change the calculation option (crosses fingers!)
 

Forum statistics

Threads
1,136,345
Messages
5,675,223
Members
419,553
Latest member
hanahass

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
Top