Disabling auto-calculate for others?

Good morning!

I'm working on a workbook that has alot of calculations in it, on a pretty slow PC (Work-related), so auto-calculate is casuing crashing, so what I've done with this sheet is disabled auto-calculate and added a macro to calculate each sheet indiviually instead, which avoids the crashing (The last sheet still takes a minute and a half to process but thats ok I guess).

My worry is, when this workbook does the rounds, peoples excel will already have autocalculate on, and will crash it themselves. Is there anyway I can ensure that the sheet turns atuocalculate on or, failing that, does anyone have any ideas on how to help the issue?

Stuart

Yes, you can use VBA in the Workbook_Open event to enforce Application.Calculation to Manual, eg:

Please Login or Register  to view this content.

However this may cause other users issues in other files etc so it's not advised really...and obviously is dependent on them enabling macros in the first instance....

I think to answer your question the key is to work out how to improve performance of your file... are you using lots of Volatile functions like INDIRECT,OFFSET,TODAY etc... are you using lots of expensive Array formulae such as CSE arrays { } or SUMPRODUCTS ?

As I say quite reguarly... if you find you have lots of Sumproducts you're best served adopting another approach... normally one of:

Please Login or Register  to view this content.

Often a) is not viable.

For b) what I mean is that you take your key criteria in the sumproduct and create a concatenation key of said values in a new column on your source sheet -- this in turn enables you to dispose of multi conditional arrays and replace with SUMIF / COUNTIF etc which are far more efficient.

If for ex we looked at Work_Data... I would say you want to create a key, let's say for args sake in Column Z) along the lines of:

Please Login or Register  to view this content.

If you go back to your main breakdown page you can then change:

Please Login or Register  to view this content.

to something less "heavy" utilising your new key in Z, eg:

Please Login or Register  to view this content.

If you post up a sample of your summary sheet I can probably help you replace them... I do this quite regularly for people and you will find that performance will improve the fewer arrays you have...
I suspect in reality your concatenation key may need to be a little more elegant your search criteria in Countif/Sumif more sophisticated as a result.

As it is, 300 Sumproducts each of which referencing say 20000 cells ... well, it's not going to be great...

hi Stuart,

DonkeyOte has this under control but if you want to learn even more yourself, you can read up a little more regarding worksheet performance at the below link (+ other frames that have links near the top of the screen) on Charles Williams' website:
http://www.decisionmodels.com/optspeed.htm

hth
Rob

Stuart,

Well... it's taken me some time to work through this model... next time I'd advise you un-hide all the relevant formulae etc as next person may not be so accommodating ;-)

Firstly I would just say that you may find it worthwhile doing a refresher on absolute & relative referencing... it must have taken you a while to put this together, utilising absolutes ($ references) & relatives would have made your life a lot easier -- XL help is pretty good tutorial on this.

Have a look and see if the approach implemented in the attached model speeds up your calculation issues.

In short it seemed that if one went across all of your various Sumproducts the following columns on CSD_Data sheet were important:

Please Login or Register  to view this content.

On CSD_Data I created a concatenation string of the values in each of the listed columns (^ delimiter) in Column W
(for column Q I did a little extra tweak to take the value and assign to the appropriate bucket (Less than 24, 24 to 29 etc...))

Once that was done I then went back and re-wrote every Sumproduct formula you had in your workbook and replaced them with COUNTIF utilising the above concatenation approach -- using wildcard (*) for those values that were irrelevant in terms of determining the appropriate count.... so if for ex. for one count only columns H & N were important (of those concatenated) you'd have a COUNTIF along the lines of:

Please Login or Register  to view this content.

So for columns F,I,K,M,O,P,Q,R,S & T we use * to imply the value in that field is not important.

The above approach using lengthy strings and wildcard's is still not a great approach (if I had the time (& inclination!) I'd be inclined to create more smaller concatenation strings and use appropriately), however, I'm intrigued to see if it speeds performance for you.

No problem - we're here to help - sad, clichéd but true nonetheless.... being a doley does have it's benefits ;-)