Hi,
Quote:
|
If i have the calculation set to Auto then it recalcs every time something is changed. Now according to my web research i am led to believe this could be due to a volatile function/formula somewhere. The problem is that unless SUM has all of a sudden become volatile then i cannot find it anywhere. The only other formulas are =A1 types.
|
This sounds correct, but SUM() is definitely not volatile.
Only volatile functions will recalculate when precedents have not changed. Do you have any named formulas in the workbook? If yes then check if any of them contain volatile functions. The most common volatile functions are OFFSET(), NOW(), TODAY() and INDIRECT(). Of course there are others, and you can find a wealth of information about Excel calculations at Charles Williams' site:
How the Excel Smart Recalculation Engine works - Decision Models
If you have conditional formatting formulas then these are also volatile by design so overloading a workbook with conditional formatting will impact performance.
Another consideration might be if you have event handler VBA code which could be calling a recalculation.
I hope that gives you some ideas - if not then perhaps you could remove any sensitive information and zip/attach the workbook for us to look at? 20 to 30 seconds is a long calculation period - if nothing else, there is a good chance we could reduce it to less than 1 second with some careful redesign and formula improvement.