This feature request is a quick fix to mitigate the risk of silently damaging formulas in many documents by inadvertently having the wrong setting for the UpdateReferenceOnSort global option. OL-Calc has 2 UpdateReferenceOnSort behaviours available through a global option, which has mooted the argument about which behaviour is broken. Both behaviours have valid use cases, and the preponderance of legacy spreadsheets has settled the default value of this option for compatibility. From a document safety consideration however, each document needs it's own persistent property for UpdateReferenceOnSort, with initial value determined by the current state of the global UpdateReferenceOnSort option. Presently, Murphy's law guarantees that the wrong global setting will be used while sorting ranges in existing documents, silently mangling cell references. In principle one document could have multiple sort ranges needing different behaviours and sort options, but OL-Calc does not support multiple persistent sort ranges in a document at this time - this could be the subject of a future feature request.
Hi @Rober, It's possible to have different ranges with their sort properties. You only nee to create a Menu/Data/Define range, Menu/Data/Select range, and define their sort properties. So for me if there is a place for the option it's the Sort properties.
(In reply to m.a.riosv from comment #1) Thanks for the tip! Defined ranges do a great job of handling multiple sort ranges having distinct sort properties (range, keys, sort options). I've changed the summary according to your suggestion, with each sort range having its own behaviour of UpdateReferenceOnSort by adding a range property. And forget the original comment about adding that global option as a new document property. So the work to do is: #1) Move the UpdateReferenceOnSort global option checkbox under Menu/Tools/Options from the LO Calc General group to the LO Calc Defaults group. It should appear under a new title "Sort Options" to distinguish it from default options for New Spreadsheet. As configured during installation, the default value for the global option will be FALSE, for compatibility with other spreadsheet software. This cosmetic UI change tells the user that the checkbox sets the default value of some other setting, rather than directly controlling some behaviour as it does now. #2) Add an UpdateReferencesOnSort range property to sort ranges (a sort range is a range with option "Operations: Sort" appearing under Menu/Data/Define Range/Options). The default value for this property is taken from the current value of the UpdateReferencesOnSort global option. The last-sorted range without a definition is preserved in the saved document. This range will have the property too. #3) Add a checkbox to the Sort Options tab of the Sort dialog to show and set the UpdateReferenceOnSort range property. #4) A legacy document doesn't have this property in its sort ranges. When loading a legacy document, add the property to every sort range using the default option value as in #2. This saves the user from manually running every sort in a document to add this property to all ranges. #5) The behaviour when sorting a range is determined by the range's UpdateReferenceOnSort range property. #6) Saving a document containing one or more ranges preserves the UpdateReferenceOnSort range property for each sort range. #7) Loading a document containing one or more ranges #8) When exporting LO to a document format that does not support the new UpdateReferenceOnSort range property, the presence of a sorted range in the document will cause the "potential data loss" warning to be displayed. == Limitations == A sort range property can't be changed by the user without performing the sort. A user can only click OK to accept changes (and sort), or click Cancel and the property lose changes. Is that acceptable behaviour?
Pinged devs on IRC and it seems this is OK to set to NEW.
This case merits consideration as a higher priority defect. The change that introduced the two sort behaviours as a global user setting has minimial impact on users who are consumers of their own spreadsheets, but it introduces ambiguity to any enterprise environment where spreadsheet documents are often shared between users.