Feb 02 2023 09:42 AM - edited Feb 02 2023 09:47 AM
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).
Feb 02 2023 10:35 AM - edited Feb 02 2023 10:40 AM
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?
Feb 02 2023 10:45 AM
Feb 02 2023 10:47 AM
Feb 02 2023 10:50 AM - edited Feb 02 2023 10:51 AM
Hm ... Not a fan of INDIRECT either.
Feb 02 2023 11:34 AM
Feb 02 2023 11:56 AM
Feb 02 2023 03:05 PM
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.
Feb 03 2023 09:46 AM
In case anyone ever finds here, I tidied and tightened up the earlier VBA code. Test sheet attached.
Feb 06 2023 11:05 AM
Feb 07 2023 04:10 AM
Feb 07 2023 04:19 AM - edited Feb 07 2023 04:28 AM
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.