Description: 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
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
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.
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!
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.