VBA: AutoCalc

[gheyman]

'=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(N11,ROW(Req_to_PO_Summary[PO_OrderReference])-ROW(N11),,1)), IF(Req_to_PO_Summary[PO_OrderReference]<>"",MATCH("~"&Req_to_PO_Summary[PO_OrderReference],Req_to_PO_Summary[PO_OrderReference]&"",0))),ROW(Req_to_PO_Summary[PO_OrderReference])-ROW(N11)+1),1))

I have the above formula in a couple of cells (looking at different rows) on one tab. Its dramatically slows down everything in my workbook - even just using a slicer takes forever. If I remove these formulas the same workbook runs very fast.

I would really hate to remove the formula because the data it produces is very valuable. I tried to find alternatives with no luck. Other formulas don't work when the data in the table is filtered.

I am trying to figure how if I can turn off the autocalc just for this one workbook and not other workbooks that may be open by the user. for this workbook I was thinking I only reclac when on the one tab that has these formulas on it either when that tab is active or even add a button to calc on demand only. Any suggestions are appreciated.

 

[diddi]

stick this in each worksheet

Sub worksheet_activate()
    Application.Calculation = xlCalculationManual  ' or xlAutomatic as required
End Sub