Download it now!
Bug 86125 - libreOffice calc formulas are loosing references when moving rows or cells
Summary: libreOffice calc formulas are loosing references when moving rows or cells
Status: RESOLVED DUPLICATE of bug 79441
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.7.2 release
Hardware: All All
: high normal
Assignee: Not Assigned
URL:
Whiteboard: target:4.2.8 target:4.3.6
Keywords:
: 85960 86227 86240 (view as bug list)
Depends on:
Blocks:
 
Reported: 2014-11-10 19:02 UTC by Erick
Modified: 2014-12-01 14:20 UTC (History)
10 users (show)

See Also:
Crash report or crash signature:


Attachments
a spreadsheet to reproduice the problem (11.98 KB, application/vnd.oasis.opendocument.spreadsheet)
2014-11-14 16:32 UTC, jacques
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Erick 2014-11-10 19:02:06 UTC
Libreoffice 4.2.7.2, kubuntu 14.04 x64

My spreadsheet formulas are getting all messed up by libreoffice calc:

for example, make a new spreadsheet. In the A1 cell, in sheet1, type in the formula:

=sheet2.B1*2

save and close.

When you open the spreadsheet again, you will notice that your formula is now

=B1*2

This is breaking up all my spreadsheets.
Comment 1 Tim Lloyd 2014-11-10 23:41:03 UTC
Hi, no problem with Fedora 21 LO 4.3.3.2

My steps

1. open new calc document
2. add a second worksheet and populat cell B1
3. Return to sheet1 and add the formula =sheet2.b1*2 to cell A1
4. Save as ods document and exit
5. Reopen document - all good

Pls let us know if there is any more detail to be included?

Cheers
Comment 2 jacques 2014-11-11 04:28:17 UTC
Libreoffice 4.2.7.2 ; windows XP

Hi
I got the same problem as Erick.

I opened an existing spreadsheet with the new libreoffice "stable" version above.
I work with it and made a lot of changes and saved it.
When I opened it later the formulas within a sheet are ok. But the formulas with reference to a cell in an other sheet are wrong : the sheet number reference is lost.

Tried to create a new speadsheet. Everything is ok while editing it and saving from time to time.
Then I closed the spreadsheet and quit libreoffice.
Then I started libreoffice again and reopened the spreadsheet. All the formulas with a reference to a cell in an other sheet are messed as Erick said.

Tim, you made your test with an other version.  

I choose to download the officially "stable" version to avoid experimenting new bugs !

This is very critical. Every body who will modify a spreadsheet with LO 4.2.7.2 will break it.
Comment 3 Tim Lloyd 2014-11-11 04:40:20 UTC
Based on jacques' comment I am setting this to NEW and leaving priority as HIGH & CRITICAL
Comment 4 Erick 2014-11-11 10:12:20 UTC
ty jaques!

Other people who complains about the same thing:
http://en.libreofficeforum.org/node/9481#comment-37075

To be fair,
the problem happens with my existing spreadsheets. I told you to create a new spreadsheet because I wanted to explain it through an easy case. I'm trying just now to replicate the problem with a new file (as I told you) but I can't.

It seen to happens only to pre-existing spreadsheets.
Comment 5 BadLar 2014-11-11 17:16:16 UTC
Same problem here.
Ubuntu 14.04 x64
Libreoffice:
Version: 4.2.7.2
Build ID: 420m0(Build:2)
Ubuntu 14.04

This just started when I Ubuntu auto updated LO.
I cannot recreate this in a new spreadsheet.
It appears to only be cell references to another sheet.
This is in a spreadsheet that I have been updating and adding to for 4 years with no issues.
I went in and repaired all my sheet references, they were broken when I opened it the next day.
Comment 6 jacques 2014-11-12 01:46:33 UTC
No Erick. It appended to me with a new spread sheet when recreating my broken spreadsheet :
all the formulas referencing a cell inside the same sheet are always ok ; but the formulas referencing a cell in an other sheet are all corrupted (the sheet number disapeared) ; also the name of a named cell disapeared (there is only one).

I took this new corrupted spreadsheet and fixed the errors step by step saving to a new file at each step. It contains only formulas ; no data. I did not get the problem again until yet.
Then I tried to delete a sheet, copy a sheet, move a sheet. Still no problem....
Comment 7 jacques 2014-11-13 19:33:57 UTC
I got it again. And I know now how to reproduce it (or how to bypass the bug).
Below is :
1. Somethong strange
2. How to reproduce it
3. Related problem

1. I have a spreadsheet that I used since the beginning of 2014. When modifying it with LO 4.2.7.2 I lost the sheet number in the formulas refering to a cell  in an other sheet. But if I open it with LO 4.2.6.3 on another computer, either sharing the disk or after a copy, the formulas are messed up but most of the value displayed are good as if the formulas are good. Very strange !

2. The bug appear when you move a row inside a sheet, or a cell inside a sheet.
To reproduce it let's create a new spreadsheet with 2 sheets,
- in sheet1 
   in cell A1 type : A
   in cell A2 type : =Sheet2.A1
   in cell A3 type Y
   in cell A4 type Z
- in sheet2 
   in cell A1 type : B
   in cell A2 type : =Sheet1.A1
- and save it.
First case :
- open this spreadsheet
- in sheet 1 move row 3 after row 4 (select row 3 and drag it after row 4 while pressing ALT key)
The sheet references in cell A2 of sheet1 and in cell A2 of sheet2 disapeared !
- quit without saving.
Second case :
- open again this spreadsheet
in sheet 2 move the content of cell A2 to cell A3
The sheet references in cell A2 of sheet1 and in cell A3 of sheet 2 also disapeared !
I suggest to modify the title to :  libreOffice calc formulas are loosing references when moving row or cell.

3. Related problem ?
On a slow computer when I move one or several rows in a  more complex sheet (even with LO 4.2.6.3) it is very long and I can see the processor use go up to 89% or more during several seconds. Much more than a normal re-calculating when I modify cell content referred by formulas.

I hope all this can help to solve the problem.
Let me know quickly if you need additional informations from me since I will fall back soon to the previous LO version.

I saw a new entry for the same problem I will mark it duplicate of this one.
Comment 8 jacques 2014-11-13 19:35:47 UTC
*** Bug 86227 has been marked as a duplicate of this bug. ***
Comment 9 jacques 2014-11-13 19:53:17 UTC
*** Bug 86240 has been marked as a duplicate of this bug. ***
Comment 10 jacques 2014-11-13 20:06:18 UTC
*** Bug 85960 has been marked as a duplicate of this bug. ***
Comment 11 BadLar 2014-11-14 01:28:50 UTC
Another update, I repaired all my cell references again yesterday, did some additions and some sorting to the spreadsheet, saving every step, all good. Had it open for approx 4 hours after opening it today while I was away, came back and I could now not type anything into any cell, yet I could still navigate through separate sheets etc. Whenever I clicked on a cell, the corresponding row and column headers would change colour, but not the cell itself, and no entry possible.

I checked the sheet that has the cell references to other sheets, and it was okay. I then closed Calc and reopened the same file, cell references that referenced another sheet were messed up again in the same way, cell reference is there and correct, the sheet reference has disappeared.
Comment 12 jacques 2014-11-14 16:26:35 UTC
(In reply to BadLar from comment #11)

 
> I checked the sheet that has the cell references to other sheets, and it was
> okay. I then closed Calc and reopened the same file, cell references that
> referenced another sheet were messed up again in the same way, cell
> reference is there and correct, the sheet reference has disappeared.

hello badlar
did not you change something before saving it ? I do not know if you see the 2 actions I identify that can make this append (moving a cell or moving a row), but thre may be  some other issue...
Until yet I have no problem working with this spreadsheet and avoiding to move cells or rows.
Comment 13 jacques 2014-11-14 16:32:11 UTC
Created attachment 109476 [details]
a spreadsheet to reproduice the problem
Comment 14 BadLar 2014-11-14 17:29:18 UTC
Hi jacques

Yes I made several changes, I changed som dates, added several rows to the bottom of my list, then did a sort based on date. I have never not been able to click in a cell, so this indeed may be a different issue.

I still have not been able to reproduce this on demand in my spreadsheet, or in several test spreadsheets.

Thanks
Comment 15 Jean-Baptiste Faure 2014-11-15 21:20:39 UTC
Set hardware and OS to ALL according to comments.

Best regards. JBF
Comment 16 Chris 2014-11-17 19:35:55 UTC
The same problem for me: A file I am using for several years now looses references to other sheets in regular intervals. It does not happen every time when I load the file and it does not happen to all references. Seems to be a bit random.

Chris
Comment 17 Shane 2014-11-18 05:40:43 UTC
Using LibreOffice Version 4.2.7.2 and also losing the Sheet Reference in cells.
This is reproducible on old spreadsheets and new.

After doing some testing, it is cause by the cut and paste of a cell. So I guess moving the contents of a cell is the same thing.

It doesn't matter if the cell you cut and paste is reference or not. All sheet references are removed. Even if you have sheet references in cells on other sheets. All sheet references are removed, but the cell reference is still there.

The cell data displayed doesn't change (very deceiving), but if you save and reload, the data now displays the new cell reference data.

The only work around I have got, is to avoid the cut and paste steps, and do a copy paste and delete.

Hope this helps in your debugging.

Kind regards
Shane.
Comment 18 Björn Michaelsen 2014-11-24 09:10:56 UTC
(In reply to jacques from comment #7)
> First case :
> - open this spreadsheet
> - in sheet 1 move row 3 after row 4 (select row 3 and drag it after row 4
> while pressing ALT key)
> The sheet references in cell A2 of sheet1 and in cell A2 of sheet2
> disapeared !

Not reproducable here with 1:4.2.7-0ubuntu2~ppa1 from https://launchpad.net/~libreoffice/+archive/ubuntu/libreoffice-4-2.
Also not reproducable on current upstream libreoffice-4-3 at https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=86863a85935516192b751bb07826fbc5487694f6.

> Second case :
> - open again this spreadsheet
> in sheet 2 move the content of cell A2 to cell A3
> The sheet references in cell A2 of sheet1 and in cell A3 of sheet 2 also
> disapeared !

Not reproducable here with 1:4.2.7-0ubuntu2~ppa1 from https://launchpad.net/~libreoffice/+archive/ubuntu/libreoffice-4-2.
Also not reproducable on current upstream libreoffice-4-3 at https://gerrit.libreoffice.org/gitweb?p=core.git;a=commit;h=86863a85935516192b751bb07826fbc5487694f6.

Thus closing as WFM.
Comment 19 diegogullo 2014-11-30 12:28:41 UTC
Same problem here 

Ubuntu 14.04 x64
Libreoffice:
Version: 4.2.7.2
Build ID: 420m0(Build:2)

The only fix I have is to keep replacing the references again to make the sheet functionality work. But this is messed up anytime i close  and reopen the document
Comment 20 Erick 2014-11-30 14:44:56 UTC
Bad news:

as you can see on
https://bugs.freedesktop.org/show_bug.cgi?id=86742

I've filled a similar bug report for libre office calc version 4.3.4.1,
as it has the same problem.