v odd formula replication!

[preqin]

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!)

 

[SteveO59L]

Is autocalc selected ?

 

[Richard Schollar]

Hi Preqin

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

Best regards

Richard

 

[preqin]

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]

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]

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

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]

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!)