F9 Recalculation too long. tipps?

[mike1984]

i work with wb's filled with GLTRAN(BSPEC(...)) formulas and the like. Every workbook has a year summary at top and the whole year below (daily activity). I am not the only user.
daily there are at least 10 people on the network accessing the same *.PST files.

does anyone know how to reduce the calculation time?

as is, i have a calculation time of minimum 3minutes to 30minutes for one workbook.

 

[indiantrix]

Would it help set the calculation to manual, and then use Shift+F9 to recalculate just the current worksheet, instead of the entire workbook?
HTH - Larry

 

[mike1984]

Thanks Larry.

already done that. the problem is that not every user knows about setting calculation to manual. so we have people recalculating the WB and saving (calculation is then set to auto-calc). next time i open that file it auto calculates... i loose 10-20mins on the recalculation.

that is another issue im working on. maybe a vba script to turn off auto-calc alltogether, just for this workbook.

 

[Blade Hunter]

Set a macro at the workbook open event which states application.calculation = xlmanual

This will force everyone's calculation to manual.

You can always put it back to automatic on the close of the workbook.

 

[mike1984]

without going into too much detail: There is one WB for each individual building. tons of buildings. each of those WB's has 365 GLTRAN(BSPEC(...)) formula's... plus in multiple columns.

i have thought about moving the GLTRAN formulas to a mastersheet. in this mastersheet i would combine all WB's. Then from the building file just direct link to the formula cells in the Mastersheet...

ultimately i believe F9 was not created to be used on a network. still, i am searching for a solution. there has to be a way to simplify this issue to save time.
anyone have other ideas? any other tips? there must be others who have similar issues?!

 

[mike1984]

Thanks Blade, will try that out tomorrow first thing.

still looking on how to cut down calculation times...