Bug 97552 - unexpected changes in formula references
Summary: unexpected changes in formula references
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2 all versions
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Cell-Reference
  Show dependency tree / graph
 
Reported: 2016-02-04 10:01 UTC by Mehmet Rıza Saraç
Modified: 2019-12-06 01:54 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:

Attachments
unexpected changes occurs in sheet ID2 (132.96 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-04 10:01 UTC, Mehmet Rıza Saraç 		Details
a smaller file (12.71 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-12 08:23 UTC, Mehmet Rıza Saraç 		Details
Sample file modified. (15.15 KB, application/vnd.oasis.opendocument.spreadsheet)
2016-02-12 23:25 UTC, m_a_riosv 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Mehmet Rıza Saraç 2016-02-04 10:01:36 UTC 
Created attachment 122372 [details]
unexpected changes occurs in sheet ID2

Some formulas that uses data from other sheets can change references unexpectedly if the source sheet cells has changed. I tried to find out if changes in references will go on other sheets but it didnt.

for example if i have a connection like 
sheet1 -> sheet2 -> sheet3 then changeing some of data in sheet1 will sometime change references in sheet2 but sheet3 formulas using results in sheet2 will not change. But if i have only sheet2 (sheet1 -> sheet2) some changes in sheet1 will change some of references in sheet2. 

I use to change data within sheet1 by -copy/paste- not by -cut/paste- so i don't expect my formula references to be changed even if my formulas has no $ sing to lock reference of cell. I have to correct this changes manually if it occurs, also not all the formulas changes, only some of them.
Comment 1 m_a_riosv 2016-02-05 00:37:11 UTC 
Please Mehmet, could you attach a sample file as minimal as possible and the steps to reproduce the issue.
Comment 2 Mehmet Rıza Saraç 2016-02-09 09:10:38 UTC 
(In reply to m.a.riosv from comment #1)
> Please Mehmet, could you attach a sample file as minimal as possible and the
> steps to reproduce the issue.

i have already attached the file that produces the problem i said. Problem occurs after some of changes made by <copy/paste> in sheet "GECE", not always but sometimes this changes cause -shift- some of the formulas in sheet "ID2". This changes affects several formulas within consecutive order in same column (for example W49---W58 or Z38--Z47). 

I can see the problem from unexpected results in sheet ID2 AJ5-AP19. Then i search for the error in rows lower than row25 and have to fix them bay <copy/past> the formulas from unaffected regions.
Comment 3 m_a_riosv 2016-02-09 10:57:14 UTC 
Sorry, but without a followed steps, it's no possible to find the issue.

What do you copy/paste? and how do you do it?
Comment 4 Mehmet Rıza Saraç 2016-02-12 08:23:45 UTC 
Created attachment 122566 [details]
a smaller file
Comment 5 Mehmet Rıza Saraç 2016-02-12 08:51:23 UTC 
(In reply to m.a.riosv from comment #3)
> Sorry, but without a followed steps, it's no possible to find the issue.
> 
> What do you copy/paste? and how do you do it?

I finally figured out how this problem appears.
The problem is about drag a region of information to another’s ones place by "mouse" drag. If both cells are referenced then references will follow moved data in new place, but reference to over written one will stay same. Accidentally making such a mistake cannot be completely fixed by < ctrl + Z >. Only the moved data will be undid, but the change in references will remain broken. Such an operation in msexcel will produce a warning for the cells to be overwritten and old references to overwritten cells will be broken with #REF info.

In example with new attachment moving data in D4-D9 region to D8-D15 then undoing, and after this moving D20-D27 to D13-D20 then <undo> will produce severe destruction in referenced calculations in columns I,K,O, and Q. We have to fix these references manually.
Comment 6 m_a_riosv 2016-02-12 23:25:21 UTC 
Created attachment 122600 [details]
Sample file modified.

There is an option to show an overwrite warning:
Menu/Tools/Options/LibreOffice calc/General

First version with the issue undoing.
Win10x64
Version: 4.2.8.2 Build Id.: 48d50dbfc06349262c9d50868e5c1f630a573ebd

Attached last sample file, with a little modification to see better what is wrong, following the last reported steps-
Comment 7 m_a_riosv 2016-02-12 23:32:57 UTC 
After a new verification first version with the issue:

Version: 4.2.0.0.beta1 Build Id: f4ca7b35f580827ad2c69ea6d29f7c9b48ebbac7

BTW hope 5.2 is used only for test purposes.
Comment 8 QA Administrators 2017-03-06 16:06:50 UTC Comment hidden (obsolete)
Comment 9 QA Administrators 2019-12-03 14:56:24 UTC Comment hidden (obsolete)
Comment 10 Mehmet Rıza Saraç 2019-12-03 22:24:11 UTC 
In version 6.3.3 now undo can fix old references.
Comment 11 m_a_riosv 2019-12-06 01:54:50 UTC 
There is not patch to fix, so the proper it's resolved as works for me.