Bug 127359 - Match formula changes, when cells it looks to are cut and pasted.
Summary: Match formula changes, when cells it looks to are cut and pasted.
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected) release
Hardware: All Windows (All)
: medium normal
Assignee: Not Assigned
Depends on:
Blocks: Calc-Function
  Show dependency treegraph
Reported: 2019-09-05 09:06 UTC by Mark Mclean
Modified: 2019-11-08 19:21 UTC (History)
4 users (show)

See Also:
Crash report or crash signature:

test case (9.68 KB, application/vnd.oasis.opendocument.spreadsheet)
2019-09-05 17:45 UTC, Oliver Brinzing

Note You need to log in before you can comment on or make changes to this bug.
Description Mark Mclean 2019-09-05 09:06:47 UTC
Match formula's do not stay the same when a cell they look at is cut and pasted to another cell. 

Steps to Reproduce:
1.In cell D1 enter.. =IF(ISNA(MATCH(A1,A2:A$5,0)),0,1) copy it down to cell d5
2.enter 1 though 5 in A1 to A5, then cut A2 and paste it to A4.
3.D2 follows A2, D2's formula is changed! That doesn't seem right. ??

Actual Results:
Match formula's in cells D1 to D5, looking at A1,to A5. If any cell in A1 through A5 is "cut and pasted, Match formula in D follows it and changes its formula to the pasted cell in A. If A2 is cut and pasted to A4, D cells change, it now looks to A1, A4, A3, A4, A5. The A2 is GONE..?? Am I not understanding how match formula works? If I copy, rather than cut, it works fine.

Expected Results:
Match formula to not change.

Reproducible: Always

User Profile Reset: No

Additional Info:
[Information automatically included from LibreOffice]
Locale: en-US
Module: SpreadsheetDocument
[Information guessed from browser]
OS: Windows (All)
OS is 64bit: no
Comment 1 Oliver Brinzing 2019-09-05 17:45:17 UTC
Created attachment 153932 [details]
test case

added test case from description

for me this seems to be the normal behaviour:
cut & paste a referrenced cell will change the formula
Comment 2 ralarock 2019-10-04 21:17:01 UTC
I was able to recreate this. When the value for A2 is CUT and PASTED into A4 the formula in cell D2 changes to "=IF(ISNA(MATCH(A4,A3:A$5,0)),0,1)". Note the "A4" in the MATCH was "A2" before the paste.

I believe (I do not know for certain) that this is normal behavior for spreadsheets. When COPY/PASTE is used with A2 the association between the formula in D2 and cell A2 remains intact. So the formula in D2 remains the same. The pasted value in cell A4 is not associated with the formula in cell D2. When CUT/PASTE is used the association between the formula in D2 and the value remain intact. When the value is PASTED into cell A4, the first parameter in the MATCH function changes to A4.
Comment 3 Mark Mclean 2019-11-08 13:39:40 UTC
Learning! Formulas are to change. But when they do change, and I ask LO to sort them, the sort follows the changes. Which it should, but then the sort is not right! I fix it by having a macro, to copy cells with formulas in them, to the cells that will be sorted! Works great now. Thanks for your time and info which helped me to rethink how to solve it. Trying to sort cells with formulas in them was new for me. Again, thanks for this, and for such a great product!
Comment 4 Eike Rathke 2019-11-08 19:21:17 UTC
See also Tools -> Options -> Calc -> General, Input Settings, Update references when sorting range of cells; which switches between two different concepts how formulas are adjusted during sort. You'll have to try out which one suits your needs better.