Non-automated and inconsistent manual updating of linked data between sheets

LO, Mojave 10.14.6

I have two sheets (‘referenced sheet’ and ‘referencing sheet’) containing data linked with a simple = formula.

  1. When the data in the cells of the referenced sheet updates, the referencing sheet fails to update automatically. This happens consistently; it never updates of its own accord notwithstanding that Autocalculate is enabled.

  2. When recalculate is used to force an update between the sheets, sometimes it works and sometimes it doesn’t.

I thought perhaps there was simply a delay, but sometimes F9 updates the data immediately, and sometimes it doesn’t at all.

Shouldn’t updating occur automatically with Autocalculate enabled, and why would F9 sometimes update and sometimes not? There seems to be no rhyme or reason to it.
If this is some kind of bug then it is a pretty major flaw; it makes aggregating data impractical, which is kind of a big deal…

Is this behaviour unique to my experience or have others experienced similar?

Referencing Sheet.ods

Referenced Sheet.ods

Recalculate Hard (Shift Command F9) is required in this instance, rather than just recalculate.