Calculation process slows down the work with xls file

[erkamu]

Hello,
I am experiencing sth annoying and I do not know how to overcome it.
So I have xls file.
Sheet 1 is the user sheet where data is entered manually.
Sheet 2 -some data base for dropdown menu.
Sheet 3 "Calculation" where I have some table with many formula that are using data from sheet 1 and different calculations are done.


The point is when I work in sheet one (apply some filters, enter some new data, etc) in the bottom of xls I get the warning :
image1.JPG

"CALCULATING: (4 PROCESSOR(S)): xx%"

and then it start counting 0%...10%...12%...69%...100%

sometimes that waiting lasts for more than 20 seconds.

I do realize that in sheet 3 I have 10-15 table with numerous formulas but I believe that my xls file is not the most complex one and there should be a way how to overcome that problem.

In the attachment you can find the test file.




Thanks a lot for your pieces of advices

Erkamu

[kev_]

You are using all the memory of your computer!!!
because:

You formatting 3,650 lines of data in sheet "sh1" - with 7 columns filed in yellow? (expensive on resources)
Also the 3,650 lines contain formula but only 611 lines used (could just more lines when required)

147 rows in sheet "Data" contain formula in 112 columns all cells with formula, some complex = 16,464 formula being evaluated (resource hogger)

Event Macro (date picker) in "Sh1" reacts to cell selection - so memory allocated to "watching"

[erkamu]

Could you please advice what to do.

[kev_]

Try this first:

When values are entered in cells, the workbook automatically recalculates all the formula in all the cells - which eats up a lot of unnecessary resources - we usually want to see the results after we finish putting in all the data.

So set "calculation" mode to manual whilst data input is taking place
Calc.jpg

When you are ready, and want to update cell formula use {SHIFT} {F9} to trigger a manual recalculation

Please come back after you have tried this and tell us how you got on.

thanks
Kev