Apache OpenOffice (AOO) Bugzilla – Issue 85147
Sort results in #REF fields
Last modified: 2008-11-06 15:24:22 UTC
A spreadsheet is created with links (using formula's like "=C33"). If these entries are sorted, they will appear as #REF and the reference to the orignal cell is lost. (Excel has the correct behaviour). Oddly enough Cut & Paste has the desired behaviour. An additional oddity is that not all formula's are invalid. If desired, an example with the incorrect behaviour can be provided. The sheet looks like the following, as described here. For column A, make a sorted list from 1 to 38 For column B, provide the following numbers ("" is for blank cells) *BEGIN* 10 11 12 "" 6 7 9 "" "" "" "" 8 1 2 3a 3b "" "" "" "" 15 4 5 22 23 21 15 27 26 25 24 14 13 17 20 19 18 17 *END* For column C, the following references are in place (rownr:formula) *BEGIN* 24:=C2 25:=C3 26:=C1 27:=C22 28:=C5 29:=C21 &"; " & C22 30:=C18 31:=C20 35:=C10 36:=C9 *END* Sort values according to column B, ascending. Rows 19,20,21 will contain =C#REF! Row 26 contains a cascaded #REF! error.
Created attachment 50792 [details] File with the error in openoffice (generated by openoffice.org 2.3.1 WinXP)
The problem is in sorting strings which does not work if the strings are in columnA and the sort key OR in another column and not the sort key. If only numeric data (with empty cells as ColumnB) is used the sort works correctly. The code may use an array index sort or a linked list sort in either case, somewhere the indicies become shuffled and the original text cannot be located. Test_6_27_08.ods contains examples.
Created attachment 54797 [details] Examples of sorts with strings and numeric data only - created with Calc 2.4.1
Checked in OOo3.0_RC1 on WindowXP. Either OOo or MS Excel has the similar result after sorting. They two result in reference error #REF. Li Meiying
For the following four cells: A2: 2 B2: a A3: 1 B3: =B2 Sort these four cells on column A, and the formula in B3, now in B2, no longer points to the 'a' character, now in B3, instead it points to B1. Behaviour is the same in Calc and in Excel. In this case perhaps it's 'obvious' what you want, but maybe you can think of other scenarios where something different may be 'obvious'. For this situation, the 'solution' is to select and copy the cells in column C (for the first attachment spreadsheet), Click Paste Special, clear Paste all and Formulas and check Numbers and click OK. (This does the same as Paste Special > Values in Excel, though it's not as intuitive.) You can then sort correctly. I realise that's not what you wanted to do, but AFAIK there isn't a way to do that. This is not a bug. Sort simply doesn't support what you want to do. If you wish to change this issue into a request for an enhancement, that's fine, but it will almost certainly need a lot of analysis to demonstrate that the change does what you want and what others might want, unambiguously, intuitively, without introducing unwanted side effects. I've set Invalid. Please revert if you don't agree. Also changed P2 to P3.
Sorry, forgot to refer to the #REF. For the small 4 cell example, the sorted formula ending up in B2 refers to the row one above (as it did before sorting). If you move the four cells to the top right-hand corner and do the sort again, the formula ends up in B1 and the cell 'one above' is outside the grid, therefore #REF is correctly generated. This will happen to any formula when sorting moves a cell so that the relative position of the cells referred to in the formula are beyond the range of the sheet.
'top right-hand' should of course be 'top left-hand'
close the invalid issue
Dear, It seems that it is obvious what I want, but there might be a case, which I cannot imagine, where it seems to be otherwise. Can you please indicate which one it is? Currently I cannot think of any such case. Obviously there is an workaround that produces the correct result, so why not do so? The point of the report is: Sort should NOT change the data, or references for that matter. It should rather REPRESENT them in different order. If A points to B, representing this, does NOT in any way change the fact that A points to B. I did not change that. Now for your example, take two tuples: <1,a> <3,#REF(1)> <2,#REF(3)> Sort then by the first column: <1,a> <2,#REF(3)> <3,#REF(1)> And decending <3,#REF(1)> <2,#REF(3)> <1,a> Obviously sorting in the second column produces an arbirary ordering, as all values (as seen by the user, of course) are equal. Please explain why breaking references is NOT a bug? Regards, - Joris
double to Issue 81349 and indeed not a bug. Excel and other spreadsheet act in the same way. It's not that easy as you may think. *** This issue has been marked as a duplicate of 81349 ***
closed double