Idea needed to stop cell from calculating to preserve its value

[ecovonrein]

The formula

F5 = IF(F4,F5,F6)

generates a circular reference when

F4 = TRUE.

That is a great shame because, while narrowly true, the reference to itself is harmless since the THEN formula does not alter the value in F5.  It would be a neat way of saying "Hang on to what you got when F4 = TRUE, otherwise refresh what you got from F6."

Any ideas how to accomplish the preservation of a value outside a VBA macro to Copy/Paste?

I have the issue of a model stepping thru time and I want to preserve the result of a plan calculation after that calculation has been turned off by the march of model time.  Or to reactivate it when resetting the model time backwards.

PS:  I don't fancy setting File/Options/Formulas/Enable iterative calculation to 1 (though I suppose that it would do the trick).

[#2]

I can't see how to do this other than with iterative calculation or VBA.

I originally posted to turn on iterative calculation but I see you don't want to do that! Any specific reason or just nervousness?

[#3]

This sort of works....

G25 is where I want my preserved calculation
F25 is set to Y to calculate, otherwise it won't

In G25 put
=LET(zz, INDIRECT("G25"), IF(F25="Y",10/5,zz))

You still get the error pop-up if you set F25 to N but it preserves the calculation anyway

[#4]

Three reasons: 1) It does not port. You can write spreadsheets like that but you cannot force the user's Excel to be configured in this way. It is an arcane non-default setting few people are familiar with. For good reasons (to follow). 2) It slows down calculations. 3) It makes the detection of accidental circular references trickier.

I am not a fan.

[#5]

Hm ... Not a fan of INDIRECT either. ;) And no fan of error pop-ups at all. :) Appreciate your input.

[#6]

They are a solid 3 reasons to avoid iteration.

INDIRECT() is Slooooow

Turn automatic calculation on and off? (at least it's on the ribbon)

Copy the formulas for each step?

Is there a way to flip-flop values across 2 cells to give last value and current value?

Maybe use that new Script thing? (sounds like VBA in disguise)

[#7]

... I've had an idea to do with a Lambda(), a sequence and a set/get instruction...

I'll try it tomorrow...

[#8]

As far as VBA involvement goes, this Module is kind-of cute:

Option Explicit

Dim liveMap As New Dictionary

Public Sub sweepDictionary(sn As String)
    If liveMap.Exists(sn) Then
        Dim d As Variant
        Set d = liveMap(sn)
    
        Dim k As Variant
        For Each k In d.keys
            Dim addrCache As Variant
            addrCache = Split(d(k), "!")
            Worksheets(addrCache(0)).Range(addrCache(1)) = Range(k)
        Next
    
        liveMap.Remove sn
    End If
End Sub

Public Function ifCache(aBool As Boolean, thenLive, elseCache) As Variant
    If aBool Then
        If Not liveMap.Exists(thenLive.Parent.Name) Then
            Set liveMap(thenLive.Parent.Name) = New Dictionary
        End If
        
        liveMap(thenLive.Parent.Name)(thenLive.Address) = _
            elseCache.Parent.Name & "!" & elseCache.Address

        ifCache = thenLive
    Else
        ifCache = elseCache
    End If
End Function

Needs to be augmented by a basic invocation code inside every Worksheet object that wants to use ifCache:

Private Sub Worksheet_Calculate()
    sweepDictionary Me.Name
End Sub

With that scaffolding in place, it is now possible to produce the desired functionality:

G5 = ifCache(F4,F5,F6)

F5 sits in the calculation tree and F6 receives a copy for as long as F4 is TRUE.  G5 receives F5 so long as F4 is TRUE, otherwise F6.

But it does require the spreadsheet receiver to enable VBA.  So if there were a Lambda solution, that would be preferable.

[#9]

In case anyone ever finds here, I tidied and tightened up the earlier VBA code.  Test sheet attached. 

[#10]

That is very neat!

Wondering if this could be a request to MS to add a self reference to a formula without having to turn on iterative calculations - e.g. a function called PREV() which would return the value of the current cell before the calculation. Then

=IF(F4,F5,F6)

becomes

=IF(F4,PREV(),F6)

[#11]

I had thought that =IF(F4;;F6) would be perfect for the purpose. The current behaviour of this illegal/meaningless statement - it produces TRUE (when F4 is TRUE) - is not intuitive.

[#12]

Incidentally, it is quite weird. The earlier ifCache works perfectly when, on reflection, it should not. In a larger production environment it transpires - unsurprisingly, really - that the formula

=ifLive(F4,F5,F6)

never extinguishes the "Calculate" indicator when F6 is updated by sweepDictionary. I had to modify the function signature to

=ifLive(F4,F5,CELL("address",F6)) (ie take elseAddr, not elseRange)

to work around this issue. Code changes are minuscule.  Update attached.

[#13]

Update:

Yesterday, a rather naughty thought crossed my mind.  Upon reflection, every cell in Excel is capable of holding TWO values:  The cell value itself (inside the calculation tree) PLUS a Note (outside the calculation tree).  A UDF, it turns out, is permitted to manipulate the Note attaching to ThisCell.  I can hence simplify ifLive materially by using that Note to cache ThisCell's previous value (avoiding the need for an extra cell inside the Worksheet and an event handler to manipulate it).