Calc: inconsistent behaviour (some values not showing) when referencing cells in another Calc document

[appreciatethehelp]

OS Mojave 10.14.6, LO V. 6.2.5.2

Am having some issues when referencing cells in another document: the values referenced show up just fine for some references, yet in others they don’t show up at all and I’m not sure why; have checked the formatting for all cells in question (which is identical), and have checked the formulae which also appear to be in order…

Attached are some examples (examples are defined by YELLOW and ORANGE cell background colours; yellow defines three separate examples of individual cell reference behaviour, and orange defines 1 example of range reference behaviour) illustrating the problem.

The cells containing the data being referenced are in “Strange behaviour when referencing cells in another spreadsheet.ods”, and the cells containing the formulae referencing said data are in “Number sets.ods”.

As you’ll see from the documents, only B3 in “Number sets” document returns the value it should- all others don’t return the referenced values… why?

[edit]
removed links for privacy reasons, pls. see comment and provide new samples,
[/edit - newbie-02]

[newbie-02]

@appreciatethehelp:
hello, i am pleased with your well presented question,
a small flaw, your sheet contains a link to google, this is not ok because it:
could publish private data of you, even if it is only your account name,
is advertising for another product,
disturbs the privacy of people who open the sheet and allow ‘update external’ without checking what is being updated,
i did not check if there is something ‘evil’ happening, things shouldn’t be like that in general, so i have hidden the files and ask you to add new examples via ‘edit’,
thank you, and my criticism or my rough intervention are not meant ‘badly’ in any way,
(that i’m unintentionally! supporting the data collection mania of google by sharpening it by preventing that many other people use your link i am aware of … skylla and charybdis …

[anon73440385]

Hello,

your formula in E12 of file Number sets.ods references a range of cells (A5:A7) in file Strange behavior when referencing cells in another spreadsheet.ods (which has its name unfairly ;-)). This range of more than one cells builds an array of values and consequently you need to use the array version of the formula, which is:

{='file:///tmp/Strange behaviour when referencing cells in another spreadsheet.ods'#$Sheet1.A5:A7}

To get the array version of a formula:

• Edit the formula as usual (here: ='file:///tmp/Strange behaviour when referencing cells in another spreadsheet.ods'#$Sheet1.A5:A7)
• Finalize the edit using CTRL+SHIFT+ENTER instead of using just ENTER (as you normally would do)

Please see the modification to file Number sets.ods

Number sets.ods

Hope that helps

[newbie-02]

@anon73440385:
well seen,
thumbs up that you’d also know how it could be done,