CODEWITHSUNDEEP
excelexcel-formulavba
LucasSeveryn
LucasSeveryn

Reputation: 6262

Prevent cell from being calculated

Is it possible to prevent calculations happening to a single cell in Excel sheet? Let's say I have 1001 cells that are very fast to calculate, but 1 cell slows sheet down, is it possible to disable calculations for that 1 cell only?

What I'm NOT trying to do:

  1. Disabling all of cell calculation programically
  2. Calculating specific cells programically while global calculation is set to manual

Upvotes: 2

Views: 9702

Answers (4)

AnotherParker
AnotherParker

Reputation: 790

Use Excel's =IF() function. It is set up to "short-circuit" -- it only evaluates the second parameter if the first parameter is true, oppositely for the third parameter.

So, if the cell is C1, and the cell's formula is currently

=LOOKUP(2,1/(A1:A100000=666),B1:B100000)

and you want it to only be calculated when D1 is true, use

=IF(D1,LOOKUP(2,1/(A1:A100000=666),B1:B100000),C1)

Notice it's a circular reference -- it's how you keep the value the same when D1 is false. Turn on iteration if you want to get rid of the warning message.

Another way is to use one of the third-party Add-Ins out there that lets you store a global variable off-sheet and then retrieve it, which would use syntax like this:

=IF(D1,SetGlobal("C1StoredCalculation",LOOKUP(2,1/(A1:A100000=666),B1:B100000)),GetGlobal("C1StoredCalculation"))

SetGlobal() and GetGlobal() can also be written in VBA, though they'll be a tiny bit slower than an XLL, and they'll lose the value if you reset your VBA project.

Upvotes: 4

Gary's Student
Gary's Student

Reputation: 96753

You can use a replacement UDF and take advantage of a lack of volatility.

Say we have a formula like:

=LOOKUP(2,1/(A1:A100000=666),B1:B100000)

Excel will re-calculate this if any cell in cols A or B change, but the UDF

Public Function myudf(r As Range) As Variant
    myudf = Evaluate("LOOKUP(2,1/(A1:A100000=666),B1:B100000)")
End Function

will only be re-calculated when its argument changes. So pick a cell and enter:

=myudf(Z100)

make any changes you want to cells in cols A or B and myudf will remain un-re-calculated until you change Z100

You can use the same tiny trick to make "quasi-volatile" versions of =TODAY() or =NOW() for =RAND()

Upvotes: 2

Charles Williams
Charles Williams

Reputation: 23520

Excel does not have a method to disable calculation for a single cell. You could move the slow formula to a separate sheet and use worksheet.enablecalculation to disable calculation for that sheet.

Or you could store the formula somewhere as text, store the result as a value in the cell, then restore the formula when you want to calculate it.

Upvotes: 2

ServerS
ServerS

Reputation: 450

I don't think this can be done. You can turn off automatic calculation in entire workbooks (as you mentioned), but I don't think there is a way to do this on an individual cell.

Upvotes: 0

Related Questions