(The above announced amendment first:) Updating formula-cells in hidden or simply not visible ranges may be deferred by Calc as long as they are not refered to by formulae in the visible range. Making such cells visible will catch up. For “history aware” cells this may cause problems. We observe a similar proble sometimes with respect to not updated ‘Pivot Tables’ or copied-to ‘Sort’ results. These also examples for the violation of what I may call the “equilibrium principle” basically obeyed by spreadsheets.
Original answer:
This is subject to speculations, I am afraid. The mandatory specification for the recalculation of OpenDocument conforming spreadsheets does not even mention one of the terms Iteration (except for a few standard functions using iterative methods internally) or Recursion.
We have to understand that allowing (possibly indirect) recursive or iterative use of formulae is completely Implementation Dependent under the blanket clause in subchapter 2.4 of the mentioned document. The first paragraph there at least demands a documentation accessible by users. However, I do not know one with the exception of the help text under “iterative references in spreadsheets” concerning the Setting of the Option. Firstly help texts are notoriously either undependable or outdated or both. Secondly the help again does not say anything about recursion (with the exception that the index delegates the term to “iterative”). Thus we have to consider that the feature exclusively is offered for purposes of iterative numerical calculations (hoping for convergence instead of proving it). Any nonnumerical usability of the feature, even if working correctly in a specific case should be considered an undocumented behaviour subject to changes without notice from version (build) to version.
My speculations now:
-
The version you are using is realy old given the breathless release plan of LibreOffice. More recent versions may avoid the malfunction you complain about.
-
I also experimented a lot with Conditionally Suspended Circular Reference (CSCR) even trying much more sophisticated formulae, but never relied on it for “production”. My experiences with the kind of rather simple use you try are not that bad. My suspicion with regard to your complaint: There is a lot to initialise when a spreadsheet is opened. Under (un)certain conditions a group of cells may be recalculated before everything they may depend on is correctly initialised. Without the CSCR used, this is not of great meaning. A second call for recalculation will repair things without your noticing the intermediary fault. Not so for CSCR for obvious reasons!
3) If I urgently needed the feature I considered two possible solutions:
3.a) Write user code for the purpose despite the fact that “macros” come with a lot of disadvantages. It may also be not quite easy to avoid the problems mentioned under 2. this way.
3.b) Try the formula =IF(NOT(B1="");B1;IF($A1=TODAY();$AnotherSheet.$H$21;B1)) or, to allow for a way of clearing the memory without need of recreating the formula apparatus
=IF($A1="Clear!";"";IF(NOT(B1="");B1;IF($A1=TODAY();$AnotherSheet.$H$21;B1)))
- Stay aware of the fact that going 3.b) will never allow recovering from any malfunction. (Except, maybe, resorting to a backup copy.) Please report your (long term) experience.
