Forum Discussion
Idea needed to stop cell from calculating to preserve its value
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.
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).