Created attachment 109499 [details] Example Formulas with unprotected (without $) references to the same row are not updated when sorting by rows in version 4.2.7.2. This problem was fixed in 4.3.3.2 but in this version protected (with $) references are also updated! For an example please see the example attached. Sort the upper table and compare with the result that should be expected below.
What is your version ? Please upgrade your installation to the just released version 4.3.4 and try again. I guess this problem is a duplicate of bug 81633 Please could you explain what _you_ mean by "updated when sorting" ? Indeed, in the second table in your test file, the references in the range A13:A15 have been clearly changed when the range A13:E15 has been sorted. They have been changed in order to keep the same values. Set status to NEEDINFO. Please set it back to UNCONFIRMED once you have provided requested informations. Thank you for your understanding. Best regards. JBF
Hi Paulo, Please could you explain why do you think that the legacy behavior is the correct one for absolute references? Don't worry, it is a real question, I am trying to understand this mess about sorting. Another thing which is confusing me is that, if you change the option UpdateReferenceOnSort from false to true in the master (next version 4.4.0) absolute references are _not_ updated when sorting the range of cells A3:E5 of your test file. You have the same behavior with 4.3.4 but there the option is hidden the expert configuration. So we can give 2 opposite definitions of updating references. Based on your test file : Definition n°1: the cell in column A corresponding to data 1, 1, 1 changes to reflect the change in the position of these data. So SUM(B$5:D$5) in A5 becomes SUM(B$3:D$3) when data 1, 1, 1 in B5:D5 move in B3:D3 Definition n°2: the formula in A5 (SUM(B$5:D$5)) is changed to keep the link with the referenced data (1, 1, 1) when this cell is moved in A3 accompanying data from B5:D5 to B3:D3 What is your own definition? Def n°1, def n°2 or other one ? ;-) Best regards. JBF
Hello Jean. Trying to respond in the same order of your questions ... 1. My versions were 4.2.7.2 and 4.3.3.2. I have right now updated to 4.3.4.1 and, btw, things are working as expected (or as I expect). Thanks. 2. "updated when sorting": Considering the example, 1st. table: Row 5 goes to row 3 when sorting. Then, the _new_ contents “=sum(b5:d5)” of cell e3 must be updated (changed) to “=sum(b3:d3)” to reflect its move 2 rows up. On the contrary, the _new_ contents “=sum($b$5:$d$5)” of cell a3 must be kept as is “=sum($b$5:$d$5)” because the rows references “5” (b and d) have “$”s protecting them. When I am talking about “updated” or “changed” I mean _after_ the move caused by the sort. When I put a $ before a row reference I want to preserve this reference in the formula. The fact of this formula being moved to another row by sorting must not modify it inside the _new_ cell. I am assuming this reference in the formula is moved during the sort but kept untouched on its destination cell. 3. About legacy ... I am a power user of spreadsheets since their very 1st. appearance, both at work and at home. This builds a “sense”. I don't know if it is a right “sense” or even a good “sense” but it is a common “sense” for those who have being using spreadsheets for long time. I recognize that there may be some situations where this behavior may have some inconveniences but, IMHO, it should be the default. This also is the behavior of other spreadsheets. 4. If I am reading your question "Definitions" right I think the Definition 1 is the correct one. To clarify here is the example: Before sorting: A B C D E 3 =SUM(B$3:D$3) 3 3 3 =SUM(B3:D3) 4 =SUM(B$4:D$4) 2 2 2 =SUM(B4:D4) 5 =SUM(B$5:D$5) 1 1 1 =SUM(B5:D5) After sorting: A B C D E 3 =SUM(B$5:D$5) 1 1 1 =SUM(B3:D3) 4 =SUM(B$4:D$4) 2 2 2 =SUM(B4:D4) 5 =SUM(B$3:D$3) 3 3 3 =SUM(B5:D5) *** 4.3.4.1 is right. So, for me, this bug can be closed. *** Regards.
Jean-Baptiste Faure, The Legacy behavior is the correct one, because that is the industry standard. Open this file in Google Sheets, Excel, or OpenOffice Calc and try to sort it. They all produce the same, correct results. This document does a nice job of explaining how relative and absolute references should be handed in a sorting routine: http://support2.microsoft.com/kb/40401
(In reply to Paulo da Silva from comment #3) > [...] > *** 4.3.4.1 is right. So, for me, this bug can be closed. *** Thank you very for your answer. So closing this bug as duplicate of bug 81633. Best regards. JBF *** This bug has been marked as a duplicate of bug 81633 ***
(In reply to Luke from comment #4) > Jean-Baptiste Faure, > The Legacy behavior is the correct one, because that is the industry > standard. Open this file in Google Sheets, Excel, or OpenOffice Calc and > try to sort it. They all produce the same, correct results. Ok, but for me it means that the behavior to be chosen is arbitrary and because we have a well established behavior, we choose this one. It does not mean that the legacy behavior is correct according to its own quality. That said I agree that for now it is the best we have. > > This document does a nice job of explaining how relative and absolute > references should be handed in a sorting routine: > http://support2.microsoft.com/kb/40401 Sorry, I am not convinced. I do not understand why this article says that the column C is correctly sorted. If we consider only the values, the order in column C is wrong after sort because the column C should be kept in the reverse order of the column A. Does that mean the column C is correctly sorted because it is not sorted at all due to absolute references? The problem with this kind of very simplified example, is that they are not self explaining why the expected behavior is the correct one. It is why I am still waiting for use-cases from the real life. Best regards. JBF