Formulas don't always update

[mrsnrub]

Hello everyone,
I haven't had a great deal of time to look into this at any great lengths, so I apologise up front if this has been answered before.

I have a colleague who has a large spreadsheet (40+ worksheets) with a number of complex formulas.

On occassion he has found that these formulas do not update when data in the spreadsheet changes, and the only workaround I have found him so far is to hit F2 to edit the cell that hasn't recalculated, and then enter. This forces the recalculation.

Obviously this can be time consuming for the large number of cells that may be affected, and can be error prone if he was not to notice that the recalculation had not been performed.

I am sure I have come across this problem before, but have never found a solution other than the F2 then Enter workaround.

Has anyone else seen this happen before and have a better way to fix it, or even a way to prevent it?

Any assistance would be appreciated.

Regards,
Steve

 

[Zathras]

See thread707-936229 for the use of Application.Volatile

 

[SkipVought]

Hi,

Check out Tools/Options/Calculation Tab

Is Automatic option selected?

Skip,

_[red][/red]

 

[mrsnrub]

Hi,
Thanks for your prompt replies.

Sorry, forgot to mention that the Automatic option is selected.

As for the use of Application.Volatile, the spreadsheet doesn't have any custom formulas. It does use in built volatile functions, so perhaps this is an avenue to investigate...

As I stated earlier, I have seen this before with other relatively large spreadsheets, I just can't recall the circumstances. I will continue to investigate and post back if I find anything useful.

Cheers,
Steve

 

[firefytr]

It's a calculation issue (i.e. Automatic, Manual). This could be a problem if your colleague has their Personal.xls files calculation property set to Manual. To check click Window | Unhide | PERSONAL.XLS | Tools | Options | Calculation (tab), check Automatic. Then Save and Hide the workbook again.

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci