[UNSOLVED/UNREPLICATED] Why inconsistent calculation outcomes with linked spreadsheets?

taxedserf · July 8, 2018, 4:03pm

LibreOffice Calc Version: 6.0.5.2 running on Linux Mint Xfce 18.3.

Two workbooks in the same linked suite of workbooks.

One workbook calls the following function and, after numerous manual attempts to update it (Edit Links > Update), eventually it pulled the correct number into the destination cell:

=IF(ISBLANK(B230),"",SUMIF('file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Transactions%202019.ods'#LUPRI,"="&B230,'file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Transactions%202019.ods'#LUV)-SUMIF('file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Transactions%202019.ods'#LUSEC,"="&B230,'file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Transactions%202019.ods'#LUV))

A different workbook in the same suite of workbooks calls the following function and, after numerous manual attempts to update it (Edit Links > Update), it fail to pull the correct number into the destination cell. The return value is incorrectly zero.

=SUMIF('file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Automatic%20accounts%202019.ods'#LUAC,B10,'file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Automatic%20accounts%202019.ods'#LUV)

These functions work correctly, without incident, in their Excel original formats in Excel 2000-2016 running on Windows 7x64 and Window 10x64 (and have been for years).

What is the root cause?

What is the end-user’s solution?

erAck · July 8, 2018, 6:59pm

A short try with an external document that contains global names LUAC and LUV pointing each to a column range where LUAC contains x and LUV contains 42 in the same row and using your second formula with the proper document name and x in cell B10 returns 42 as it should.

If you enter ='file:///home/user/grive/PRIV/Accounts/Ye2019%20Linux/accs-%20Automatic%20accounts%202019.ods'#LUAC as array formula (close with Shift+Ctrl+Enter), does it display the expected values of LUAC?

taxedserf · July 13, 2018, 9:17pm

No. It fails to create an array formula (I’m expecting enclosure by { and }, as in Excel). Thus, there is no change, the formula that doesn’t work still doesn’t work.

taxedserf · July 13, 2018, 9:38pm

I have created a redacted version of the two sheets, with images of their content in their “live” environment.
https://drive.google.com/file/d/1t7wMikqmuBLNNvBNxIfvLxrnlMak_TPo/view?usp=sharing

erAck · July 14, 2018, 6:21pm

It would be nice if at least the file name of the external referenced document would match the name used in the formula expression… However, the file accs- Automatic accounts 2019 debug anon.ods does not have any named ranges or database ranges, so that example can’t work anyway.

erAck · July 14, 2018, 6:22pm

For the array formula test, make sure you close the input with Shift+Ctrl+Enter

taxedserf · July 21, 2018, 6:44pm

Looks like the redaction process removed the named ranges, which didn’t help (!!), and the files needed to be renamed to make them different from the originals. Test data clearly flawed. It demonstrates why testing spreadsheets is actually rock hard. I’ll close this issue as unsolved/unreplicated and raise another issue when the incident recurs.

It’s frustrating, because I cannot get Calc to work as reliably as Excel. It undermines the project to leave Microsoft!