Is there a way to stop a formula from updating after a certain criteria is matched?
For example:
A1 = 1
B1 = '=A1*2'
Lets say this is the current one. Tomorrow data will change
A1 = 2
B1 = '=A1*2'
I need to be able to fix the value of cell B1 at 2 (1*2) and not have it update to 4 (2*2). The trigger should be the date.
The values in A1 will switch dynamically; I can't stop that, I just need the ability to stop other cells from updating after a date is matched.
A VBA answer. This one works even with circular references disabled (though it is less flexible). It defines two worksheet functions which can selectively either evaluate formulas in a cell or freeze them, depending on the condition:
Function EvaluateIf(expression As String, condition As Boolean) As Variant
Application.Volatile
Dim myText As String
Dim myVal As Variant
If condition Then
myVal = Application.Evaluate(expression)
Else
myText = Application.Caller.Text
If IsNumeric(myText) Then
myVal = Val(myText)
Else
myVal = myText
End If
End If
EvaluateIf = myVal
End Function
Function FreezeAfter(expression As String, deadline As Date) As Variant
Application.Volatile
Dim myText As String
Dim myVal As Variant
If Now > deadline Then
myText = Application.Caller.Text
If IsNumeric(myText) Then
myVal = Val(myText)
Else
myVal = myText
End If
Else
myVal = Application.Evaluate(expression)
End If
FreezeAfter = myVal
End Function
To illustrate their use. If in B1 you enter =EvaluateIf("2*A1",C1) then when C1 contains =True() B1 updates with A1 but if C1 has =False() then B1 stays frozen. For the second function, if in B2 you enter =FreezeAfter("A1*2",C2) and if in C2 you have something like 6/25/2015 1:00:00 PM then the formula in B2 will update with A1 prior to 1:00 PM but will remain frozen afterwards.
Of the two approaches (the circular vs. VBA) I suspect that the non-VBA is probably more efficient and possibly more reliable (I haven't tested the VBA approach with a wide variety of functions). On the other hand -- enabling circular references could potentially cause problems (it isn't turned off by default for no reason).
You can use a circular reference. For example, in A2 I entered
=IF(NOW() < C1,2*A1,A2)
C1 has the value 6/24/2015 14:39
I enable circular references underneath file > options > formulas. Prior to 2:39 my time I was able to change the value of A1 and see A2 change. That was 2 minutes ago (in my time zone). Now when I change A1 the value of A2 stays fixed.
There is no simple way to do this. I simply added two timestamp cells containing the @NOW() function, formatted to display time. The other contains the @Today to show date. I then copy paste special to the correct cell keeping values and source formatting.
