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...
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?
- 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. - 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 anywayHm ... 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)
