Created attachment 79362 [details]
cells with "0" and the formula with broken relation
I read out values from another spreadsheets to sample and use then other formulas.
When I sort the rows by information in certain columns other than the first column I get e.g. "#REF" for some cells. These and some other cells show "0"
Using the first column a sort column not all the relations are broken or "0".
But this refuses to save and reopen zo get the right informations.
Steps to reproduce:
1. Produce 2 spreadsheets with relations
2. try to sort the "sampler" with formula for relations
most of values get "0". Most of these cells show #REF n the formula
sorting by values in the cells
Operating System: Mac OS X
Version: 184.108.40.206 release
Not reproducible on LO 220.127.116.11 (Win7 32bit)
But I found another weird behavior -> after sorting based on column (not first column) with formula linked with another file, that column not moving but others yes
Maybe a special "feature" on Mac?
Further now I found some more strange behavior:
Changes in the source are not actualized. Even not when manually actualized through "Edit - Relations". Only after quitting LibreOffice and reopen.
After last try I mention the marked cells are actualized only!
Sounds like a duplicate of this bug to me. If you disagree please let us know how it is different set the bug to UNCONFIRMED again (you may have to set to NEEDINFO first, apologies for this)
*** This bug has been marked as a duplicate of bug 45146 ***
Sure in both cases the result is wrong.
The difference is that I do not get other results but a "0" in most the reference cells.
The programming reason for may be the same. The behavior seems to be another and to in the lost connection to the reference file...
2. The actualizing in the marked cells only may be an extra theme. But the unexpected -and not reported - behavior leads to fails...
IMO your report looks same as Bug 45146 because you didn't provide some sample spreadsheet & how to see problem on that sample. At a glance it looks the same.
So please supply us with materials needed to test. We need to know cell contents which generate #REF & 0. I also want to confirm my finding on Comment 1.
Created attachment 80031 [details]
Let me try with a simple test sample. I attached Test1.xls & Test2.xls later.
To see the problem: (Problem A)
1. Open Test1.xls
2. Cells B1-B5 is linked to Sheet1.A1-A5 on Test2.xls
-> change cells B1-B5 to appropriate location of Test2.xls
(eg. cell B1 ='file:///C:/Users/xxxx/Test2.xls'#$Sheet1.A1)
3. Mark all rows A1-A9 > Data > Sort > Column C > Ascending > OK
4. See that B6-B9 returns 0 because that cells produce wrong reference on Test2.xls
Problem A: reference to another file not remembered properly while sorting
@winkelmann: is that what you mean? similar with bug 45146
Another problem: (Problem B)
1. & 2. same as above
3. Mark all rows A1-A5 > Data > Sort > Column B > Descending > OK
4. See that B1-B5 don't change, seems like not moving at all
Problem B: column that has reference to another file not moving while sorting if the column used as Sort Key
Created attachment 80032 [details]
sorry for the delay. I had much work and this problem came up through this work.
the described (Problem A) is the problem I am taling about.
> 1. Open Test1.xls
> 2. Cells B1-B5 is linked to Sheet1.A1-A5 on Test2.xls
> -> change cells B1-B5 to appropriate location of Test2.xls
> (eg. cell B1 ='file:///C:/Users/xxxx/Test2.xls'#$Sheet1.A1)
> 3. Mark all rows A1-A9 > Data > Sort > Column C > Ascending > OK
> 4. See that B6-B9 returns 0 because that cells produce wrong reference on
> Problem A: reference to another file not remembered properly while sorting
> @winkelmann: is that what you mean? similar with bug 45146
But I don't know if I refind it in this bug 45146.
Sometimes the result is "0". Some rows loose their reference...
This bug deals with *external* references. Bug 45146 deals with *internal* references to the same sheet.
Change duplicate to bug 77018
*** This bug has been marked as a duplicate of bug 77018 ***