Apache OpenOffice (AOO) Bugzilla – Issue 92604
sorting fails when formulas are used
Last modified: 2008-10-30 12:04:20 UTC
I have attached a spreadsheet to demonstrate the failing behaviour. Select area A2:C64 and then click Data | Sort --> OK ^^ As can be seen the formulas have been corrupted.
Created attachment 55662 [details] Try sorting A2:C64 and you will that the formulas will be corrupted by the sort process
Please have a look at issue 61936. I think that is the same problem.
This issue is similar but different to issue 61936. The sort function is buggy and I have created another spreadsheet called "sortingwithformulasV2.ods" to demonstrate this. Open up sortingwithformulasV2.ods and select area A2:C4 and click on "Data | Sort | OK". It appears the sort function is modifying the relative references based on how far up or down a row moves during the sort. Before the sort this row: 08/08/2008 =F71/4 Rent was on row 2. After the sort it had moved down one row and so it then modified the relative reference down by one: 08/08/2008 =F72/4 Rent Now have a look at these rows: 11/08/2008 =F72 Food 18/08/2008 =F72 Food 25/08/2008 =F72 Food 01/09/2008 =F72 Food 08/09/2008 =F72 Food which before the sort where on rows 15-19. After the sort these rows now occupy 4-8 and have a look how the data has been modified: 11/08/2008 =F61 Food 18/08/2008 =F61 Food 25/08/2008 =F61 Food 01/09/2008 =F61 Food 08/09/2008 =F61 Food and also have a look at row 10: 15/09/2008 =F62 Food ^^ It can clearly be seen that the sort function is modifying relative references inconsistently changing behaviour radically especially if you use a function. This should remain as a P2 priority because "User data is corrupted in an easy-to-encounter way". The "Data | Sort" function should include a new option in its "Options" page of "Preserve relative references" which by default should be ticked as this would be the behaviour the majority of users would expect when performing a sort.
Created attachment 55664 [details] Buggy sort behaviour when relative references and formulas are used
Behaviour is correct! After sorting, the functions refer exactly to the same offset of columns and rows, they did before sorting. That is exactly what relative references are meant for. For your use case you must not use relative but absolute references. --> Resolution: INVALID
set to invalid as suggested
closed