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.
I am not a fan.