2

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.

PW Kad
  • 14,953
  • 7
  • 49
  • 82
Avinta
  • 678
  • 1
  • 9
  • 26
  • Have you tried an `if` statement? In Excel cell type `if(logic_test, [value_if_true], [value_if_false])`. You should be able to make your logic test a check of a date. – Mike Z Jun 23 '15 at 21:33
  • @MikeZ, I was thinking an `if()` here too, but that doesn't really help with saving the previously calculated value. `B1` needs to stay `2` and `if()` won't know that it was two, just that you don't want it to be `4` because of the date. – JNevill Jun 23 '15 at 21:35
  • That is exectly my problem at the moment cuz this was my idea in the first place. I though about using a new row which copies the value of the adjusting one as long as the criteria isn't matched. But i don't know if there is a way to copying the value besides writing '=[cellname]' as this won't help – Avinta Jun 23 '15 at 22:21
  • You might be able to do this with a combination of fiddly Macros, but I'm not sure of any way to do it with Excel alone. Either using a Macro or manually, you could have an external sheet with all the values of A1 and could give each a date. Then you could have an IF to only show the latest answer within the appropriate dates. I can't think how you would do it otherwise. – Trum Jun 24 '15 at 15:30

3 Answers3

3

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).

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • Thanks...will try this one later on. For now the formula version with the circular references is sufficient. Appreciate your help – Avinta Jun 27 '15 at 21:52
2

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.

John Coleman
  • 51,337
  • 7
  • 54
  • 119
  • Yeah i was thinking in the same direction but this doesn't solve my problem or i am doing it wrong. I assume you are talking about B1 changing and not A2 The Problem is that there is no value A2. It's only a value in A1 and i need that value in B1 until the date matches ... but A1 is changing after, but B1 shouldn't. I don't think there is an easy solution anymore...too bad – Avinta Jun 24 '15 at 19:35
  • B1 was a typo. I fixed it. I entered the formula in cell A2. You should be able to reproduce my experiment. As it happens -- I still had that spreadsheet open. A2 is still unresponsive. But (hold on a second) - I just changed the time in C1 to 3:40 PM (2 minutes in the future) and now A2 changes with A1... Now it is 3:40 and A2 stays fixed again even when A1 is changed. – John Coleman Jun 24 '15 at 19:41
  • Yeah i tried to reproduce your sheet but actually wasn't able to as i misunderstood the typo and was confused cuz you talked about circular references. I entered the formula in B1. Now with the edited version it is exactly what i was looking for. – Avinta Jun 25 '15 at 15:01
  • Glad it worked. I sometimes have Excel dyslexia and tend to confuse things like A2 and B1. Maybe I played the game Battleship too much as a kid. – John Coleman Jun 25 '15 at 16:12
0

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.

Aviel Shapira
  • 1