Description: I get unexpected result when ordering a simple table using autofilter Steps to Reproduce: 1. In the test file ('initial' tab), you'll see a table with 10 items and their price. In the 2nd tab ('discount') the '50% Discount' column just calculates half of the initial price. In both tabs, there is an autofilter applied to the 1st rows, and the items are sorted ascending by the first column ('#'). 2. Our objective is to 'sort ascending' the items in the 'discount' tab according to their final prices (i.e., column '50% Discount'), irrespective of the sort order of the 'initial' tab. 3. Go to 'discount', and in the autofilter of the '50% Discount' column apply 'sort ascending'. Actual Results: It works, and I put a values-only copy on the tab "should be". BUT... now if you go back to 'initial' tab and 'sort ascending' by 'Initial Price', check again the '50% Discount' column of 'discount' tab ... it changed!! (you can compare to the tab 'should be'). For example now the cell with the chair (tab 'discount', cell C4) is being calculated using the price of the pillow!! (tab 'initial', C2). Expected Results: I expect that the 'discount' tab will not change, no matter how I sort the table in 'initial' tab. I understand that '50% Discount' column in 'discount' tab is calculated using 'Initial Price' from 'initial' tab, but I am not really changing any data from the 'initial' tab, only merely the way (order) it is shown, so I expect that the 'discount' tab should also not change at all. Besides, the formulas are of the type '=$initial.C2' which means that only the tab name is fixed, not the column or the row, therefore I do not expect that it references always the same address (which would be expected if I used '=$initial.$C$2'). Reproducible: Always User Profile Reset: No Additional Info: Either is is an error or I am very wrong in my expectations. If it is an error, it is a quite serious one because (in my probably wrong expectation) this is something the user doesn't check often and gives for granted, I was only able to detect it by chance... otherwise it would mean a pretty serious error and headaches to find and understand. If I'm wrong please tell me what would be a better practice for this (don't tell me 'put everything in the same tab', as it would completely defeat the purpose of a multiple spreadsheet program) Version: 24.2.4.2 (X86_64) / LibreOffice Community Build ID: 420(Build:2) CPU threads: 24; OS: Linux 6.5; UI render: default; VCL: qt5 (cairo+xcb) Locale: en-US (en_US.UTF-8); UI: en-US Ubuntu package version: 4:24.2.4~rc2-0ubuntu0.22.04.1~lo1 Calc: CL threaded
Created attachment 194929 [details] test file
I can confirm the misbehavior! Sitenote: If you start with sorting in the »discount«-Sheet, the Formula-refences to their original source are kept, but unfortunatly not if you start with sorting on »initial« Independend from bug or not, I would strongly recommend using a more robust design, with a Formula like: ``` =VLOOKUP($B2;$initial.$B$2:$C$11;2;0)/2 ``` in »discount.C2«
(In reply to Werner Tietz from comment #2) > Independend from bug or not, I would strongly recommend using a more robust > design, with a Formula like: > ``` > =VLOOKUP($B2;$initial.$B$2:$C$11;2;0)/2 > ``` > in »discount.C2« Or, if possible, just use an additional column ("discount") as part of the original initial table, instead of having it in an independent table on a different worksheet. To be clear, I have not analyzed the case, so IDK whether there is some bug or not. I am simply making a suggestion in the same direction as the quoted text.
Maybe the issue is with the option: Menu/Tools/Options/LibreOffice Calc/General/Update references when sorting range of cells.
(In reply to m_a_riosv from comment #4) > Maybe the issue is with the option: > Menu/Tools/Options/LibreOffice Calc/General/Update references when sorting > range of cells. +1 ( the issue happens __without__ this Option … but not with it)
(In reply to Werner Tietz from comment #5) > (In reply to m_a_riosv from comment #4) > > Maybe the issue is with the option: > > Menu/Tools/Options/LibreOffice Calc/General/Update references when sorting > > range of cells. > > +1 > ( the issue happens __without__ this Option … but not with it) I was not aware of this setting. Therefore, I guess this thread should turn into: why is this option not enabled by default? Is it not the expected behavior in most cases? Actually I would go further and ask: why would anyone want to disable updating the references when sorting? And, it is not a matter of taste, in the case I presented the formula is clearly doing the job in a wrong way (e.g. calculating the discount for one item based on a completely different one, silently). Anyway, I plea for a 'enabled' default. I find it difficult to see how the 'don't update references when sorting' mode is the preferable, expected behavior for most users. Maybe I'm missing something and there are many cases when one would not want the references to be updated? (and risk incurring in 'hidden' errors as the one I presented?)
Changed the title from 'sort gives unexpected results' to 'Enable 'Update references when sorting range' by default'
(In reply to J22Gim from comment #6) > Maybe I'm missing something and there are many cases when one would not want > the references to be updated? (and risk incurring in 'hidden' errors as the > one I presented?) As suggested, there other possible layouts and formulas that would avoid your problem, whichever that setting would be. Even using the same formulas and layouts as in attachment 194929 [details], there might be possible cases in which the current setting is adequate. For instance, there could be a case in which you always want to follow the specific location ($initial.C2), whichever value ends up in-there. If you would changed the formula in the 'discount' worksheet, cell C2, from: =$initial.C2/2 to: =$initial.$C$2/2 ...then it should track the specific cell, even after re-sorting the data. BUT, the "problem" is that then you cannot simply copy (or fill down) the formula to the rest of the cells down the column. Other layouts and/or other formulas (as suggested in prior comments) should work better. As for changing the default behavior of "Update references when sorting range of cells", I'm not sure that's a good idea at this point. First, it would change the default behavior for everyone. What would happen when a user already expects the current behavior to be the default (e.g. on a new installation of LO)? Suddenly, the results would be different, without being aware of it. It would also help to know what's the current behavior on other spreadsheet tools, before thinking about introducing disrupting changes. Or, maybe, this setting is there exactly for this reason: to make Calc behave as other tools, per users' choice.
(In reply to ady from comment #8) > If you would changed the formula in the 'discount' worksheet, cell C2, from: > > =$initial.C2/2 > > to: > > =$initial.$C$2/2 > > ...then it should track the specific cell, even after re-sorting the data. Apologies. I have to correct my own statement. The cell is tracked when manually moving it, not when using data sort. The rest of my comment 8 is still valid.
(In reply to ady from comment #3) ... > Or, if possible, just use an additional column ("discount") as part of the > original initial table, instead of having it in an independent table on a > different worksheet. > I have to repeat myself here: this completely defeats the purpose of using a multiple-spreadsheet-capable program. There are infinite reasons why one would need to have the discount column in a separate table while keeping it linked to the 'original' table. Yes, one can argue and discuss different ways of doing it, more or less efficient, more or less readable, etc. But the case I pointed out results in a plain wrong calculation (not a matter of opinion), a silent mistake that would probably be impossible to detect in a large table. I guess what I am trying to say has two parts: FACT: with the 'Update references when sorting range' enabled by default, a 'normal' user will end up with the 'chair' cell (tab 'discount', cell C4) being calculated using the price of the pillow!! (tab 'initial', C2). This is clearly a mistake. OPINION: What is the cause of that mistake? A) A misuse of Calc? (Note I am not referring to a sub-optimal use which would lead to less efficient workflow, etc. but a MISUSE, that would translate into a MISTAKE). In this case, it would be nice to learn what is the 'correct' use (and again, I am not referring to 'put the column in the same tab' or 'use =$tabname.$C$2' solutions because they obviously defeat the purpose of using a spreadsheet). B) An incorrect default option (enabled) of 'Update references when sorting range' option? C) Something else that I am missing entirely? If A) is the reason, then I would be surprised to find out what the 'correct' use of Calc is. If B) is the reason, well, there must be a good justification that this conversation is missing up to now. I agree that it would help to know - what is the default behavior of other spreadsheet programs - why that option exist in the first place Maybe someone from the developers' team can chime in? Or even better, the UX team (to comment on why this is the sensitive default behavior).
(In reply to ady from comment #8) > As for changing the default behavior of "Update references when sorting > range of cells", I'm not sure that's a good idea at this point. First, it > would change the default behavior for everyone. What would happen when a > user already expects the current behavior to be the default (e.g. on a new > installation of LO)? Suddenly, the results would be different, without being > aware of it. > While I agree as a general approach, it is not too much of a big deal (it has to be very well justified, of course). It's a bit too early to discuss this, but if it turns out it deserves to be changed because it will improve the product, then why not? The way it is right now seems quite 'dangerous' to me because it creates room for 'silent' errors, which are often the most difficult to track and find. Maybe we should think of scenarios where changing this would negatively affect a given spreadsheet. Your sentence "What would happen when a user already expects the current behavior to be the default (e.g. on a new installation of LO)? Suddenly, the results would be different, without being aware of it." is very close to what happened to me: I had some expectations and they were not fulfilled. The question is the same for me and your potential user: were those expectations reasonable? were they based on sound reasoning or just historical reasons? can it be improved?
I have checked two things 1) in the Help of Calc > Tools > General I found no mention to the 'Update references when sorting range of cells' (https://help.libreoffice.org/latest/en-US/text/shared/optionen/01060300.html?&DbPAR=SHARED&System=UNIX) 2) I created the same file in Google Sheets and the behavior is the same as the default one in Calc (ie, with 'Update references when sorting range of cells' disabled). In Google Sheets there is not even an option to change this behavior (good for Calc!). If we trust Google did a thoughtful design of their Google Sheets (i.e. the chances Google got it wrong is very low), then I have to accept that my expectations are wrong and/or I am simply misusing Calc and I have to avoid this kind of situations in the future.
CC'ing Olivier just for this comment: (In reply to J22Gim from comment #12) > in the Help of Calc > Tools > General I found no mention to the 'Update > references when sorting range of cells' True. There is no mention of: "Update references when sorting range of cells" in: <https://help.libreoffice.org/latest/en-US/text/shared/optionen/01060300.html> ...nor in the master branch. There should be. The item is mentioned in the Calc Guide.