Excel: Turn off Auto-Calculate for ONE sheet only? 1

[VBAjedi]

Is it possible in Excel to turn off Auto-Calculate for one sheet only (preferrably using VBA)? I have one sheet with complex (and SLOW) calculations (which reference the other sheets). I only want this sheet to calculate when it is activated, but I want the rest of the workbook to Auto-Calculate.

Any ideas?

Thanks!
VBAjedi

 

[Zathras]

Pretty simple, actually. Set recalc to Manual (which I assume you have done) and put this code in every other sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  ActiveSheet.Calculate
End Sub

But perhaps the slow calculations could be speeded up with different techniques? (Or a faster computer )

 

[VBAjedi]

That's what I needed! Thanks, Zathras.

As far as speeding up the calculations: Yes, a faster computer would be nice. As for different techniques: be my guest! This spreadsheet is pretty complicated, and I haven't found any significant ways to speed it up. The problem is four formulas that are on each of 50-100 rows:

=COUNTIF(ContractHistoryRange, A6)

=IF(G6>0, SUM(IF(ContractHistoryRange=A6,ValueScoresRange)) / G6, 0)

=COUNTIF(ContractFutureRange, A6)

=IF(OR(I6>0, G6>0), SUM(IF(ContractAllRange=A6, ValueScoresRange)) / (I6 + G6), 0)

The second and fourth formulas are array formulas, and they are what take the most time (especially multiplied 50 to 100 times!).

If you really want to see this, I could email you a copy of the workbook. It's a pretty interesting specimen, if I do say so myself.


VBAjedi

 

[Zathras]

Sounds like database formulas might be useful, but can't be sure from your description.

Sorry, but as a matter of policy I never post my e-mail address in a forum.

Perhaps Dale might be interested in helping you with this. If he doesn't get around to reading this particular thread, you might re-post with "Challenge for Dale" in the subject.

Geoff might jump in here too, if he sees it.