Forum Discussion
Idea needed to stop cell from calculating to preserve its value
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 for...
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
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
Hm ... Not a fan of INDIRECT either. 😉 And no fan of error pop-ups at all. 🙂 Appreciate your input.
- 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)- ... I've had an idea to do with a Lambda(), a sequence and a set/get instruction...
I'll try it tomorrow...