F9 Recalculation too long. tipps?

mike1984

New Member
Joined
Sep 4, 2009
Messages
20
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

indiantrix

Well-known Member
Joined
Oct 17, 2002
Messages
943
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

New Member
Joined
Sep 4, 2009
Messages
20
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

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
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

New Member
Joined
Sep 4, 2009
Messages
20
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

New Member
Joined
Sep 4, 2009
Messages
20
Thanks Blade, will try that out tomorrow first thing.

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

Watch MrExcel Video

Forum statistics

Threads
1,123,270
Messages
5,600,637
Members
414,398
Latest member
dhune

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top