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.
