Bug 64647 - EDITING: Losing REF to related spreadsheet after sorting
Summary: EDITING: Losing REF to related spreadsheet after sorting
Status: RESOLVED DUPLICATE of bug 77018
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.0.3.3 release
Hardware: Other macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard: BSA
Keywords:
Depends on:
Blocks:
 
Reported: 2013-05-15 19:34 UTC by g_winkelmann
Modified: 2014-06-03 13:37 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
cells with "0" and the formula with broken relation (123.51 KB, image/png)
2013-05-15 19:34 UTC, g_winkelmann
Details
Working file (6.00 KB, application/vnd.ms-excel)
2013-05-30 11:14 UTC, ign_christian
Details
Reference file (5.50 KB, application/vnd.ms-excel)
2013-05-30 11:15 UTC, ign_christian
Details

Note You need to log in before you can comment on or make changes to this bug.
Description g_winkelmann 2013-05-15 19:34:35 UTC
Created attachment 79362 [details]
cells with "0" and the formula with broken relation

Problem description: 
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

Current behavior:
most of values get "0". Most of these cells show #REF n the formula

Expected behavior:
sorting by values in the cells
              
Operating System: Mac OS X
Version: 4.0.3.3 release
Comment 1 ign_christian 2013-05-16 03:21:32 UTC
Not reproducible on LO 4.0.3.3 (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
Comment 2 g_winkelmann 2013-05-24 11:51:26 UTC
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!
Comment 3 Joel Madero 2013-05-24 15:10:28 UTC
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 ***
Comment 4 g_winkelmann 2013-05-29 09:51:31 UTC
I disagree.

1.
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...
Comment 5 ign_christian 2013-05-30 10:28:21 UTC
@g_winkelmann,

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.
Comment 6 ign_christian 2013-05-30 11:14:33 UTC
Created attachment 80031 [details]
Working file

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
Comment 7 ign_christian 2013-05-30 11:15:29 UTC
Created attachment 80032 [details]
Reference file
Comment 8 g_winkelmann 2013-08-02 15:26:32 UTC
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 
> Test2.xls
> 
> 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...
Comment 9 Laurent Balland 2014-06-03 13:37:05 UTC
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 ***