(In reply to Colin from comment #0)


> 
> Actual Results:
> Current dogma prevents many valid simple sorting requests on validated cells
> 
Should read "......protected cells".

Validation techniques forcing the cell to validate;

     10*the square root of the day before yesterday MINUS the current cell value

seems a little tortuous compared with MENU>Data>Validity>Allow  - No User Input

with the help and error message of "No User Input"

(In reply to Colin from comment #0)
> Description:
> If a cell in a filtered array is defined as protected then sorting of the
> array is prohibited.
> The reason for this is stated as "It is not possible to sort / reorder
> protected rows, because by doing that the cells would change their contents
> (example: the value at cell A1 - that is, column A, row 1 - would be moved
> to another row). (Also, the sorting options in the autofilter menu do the
> same that the sorting tools in the toolbars.)"
> If the entire row is based upon recalculating values contained entirely
> within that row or with absolute addresses from outside the filtered array
> then the above excuse seems flawed.
> If an additional data validation of "No User Input" were assigned to a cell
> or group of cells then acceptable checks and balances could be included in
> the formulae for those cells.
> Seems like a simple Win-Win scenario to me.

Came here to post a similar bug report / feature request.

My use case is analyzing a CSV file by applying different sorts and filters to its rows and columns. I should be able to prevent changing cell contents only, without losing the ability to sort, filter, reorder, add, remove rows and columns.

When I load a CSV, each and every cell have "Cell Protection" attribute set / tickmark checked. My expectation is that "Cell Protection" disables modifying cell contents (which is what I want), but ticking and unticking that "Cell Protection" tickmark has no observable effect - the cell content can be changed regardless. Internet search results suggest that "Cell Protection" has no effect unless "Protect Sheet..." is enabled, in another top level menu "Tools" separated from "Edit" menu by 6 other top level menus. Enabling "Protect Sheet..." disables cell editing, which is what I need; but it also disables sorting and filtering of rows and columns, which is not what I need.

That's rather user-unfriendly and undesirable functionality, in my opinion. Not completely worthless, though, because such a design and functionality can still be used as bad examples in textbooks to contrast with the fundamental engineering principle of least astonishment. 

LibreOffice calc must be able to prevent cell contents from editing: make a cell read-only or immutable. Without affecting anything else such as the attributes of row and column the cell is an element of. A read-only cell must remain within its row or column when those are sorted, reordered or filtered.

In the ideal world, the existing "protection" functionality would stay as it is to avoid breaking existing spreadsheets (or, if nothing else, for posterity). And new, user-friendly, straight-forward, one-click, least-astonishment functionality would be implemented:

1. Read-only cells. Prevent from editing cell contents or formula. Must not affect formula recalculation, or any of row or column attributes a cell is an element of. Must not affect reordering, adding or removing of rows or columns. 
2. Marking a cell read-only should take at most one keystroke / keyboard shortcut, be in the top level of "Edit" menu, be in the top level of row/column context menu, be a one-click icon in the menu bar.
3. Read-only rows. Apply read-only attribute to all cells in a row. Must not affect reordering, adding or removing of columns.
4. Read-only columns. Apply read-only attribute to all cells in a column. Must not affect reordering, adding or removing of rows.

I started using LibreOffice Calc actively only this year and already have plenty of other ideas that would obsolete Microsoft Excel and make it legacy product, if LibreOffice Calc had resources and skill to implement.

Maxim

The version I comment on:

Version: 7.3.5.2 / LibreOffice Community
Build ID: 30(Build:2)
CPU threads: 32; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb)
Locale: en-GB (en_GB.UTF-8); UI: en-US
Ubuntu package version: 1:7.3.5~rc2-0ubuntu0.20.04.1~lo1
Calc: threaded

Please provide an example to follow your steps. It's a quite complicated topic.

(In reply to Heiko Tietze from comment #4)
> Please provide an example to follow your steps. It's a quite complicated
> topic.

Hi Heiko,
I assume you're referring to Maxim's comments.
Perhaps I found it easier to follow them because I raised the issue but I think the simple version is;

1. Permit the use of a validation/cell protection "NO USER INPUT" which will obviously have zero impact on any cell calculation anywhere. It's a simple mechanism to avoid a user inadvertantly overwriting a calculated cell.

2. Permit the setting for an entire selected column, row or matrix.

3. Ensure this permits AUTOFILTER sorting functionality which is currently prohibited with protection for the specious reasons given in the extract from the help file.

I say specious, because LoCalc can already retain links between cells in both the current sheet and other referenced sheets when sorting and filtering, so why this should be an issue if a user is prohibited from modifying a computed value eludes me.

I've left it as need info but you may need to target Maxim's email as he's NOT attached to the CC list. If it's targeted at me then please advise if further information is required

We discussed this topic in the design meeting.

To rephrase the request, it should be possible to sort data in protected sheets (filtering works out of the box). Sorting may affect the data integrity, for example if a reference changes after sorting. We should also keep in mind that you can limit sort to ranges. So this option would be a bit dangerous, yet there might be scenarios where it is acceptable and needed.

Instead of tweaking data validation with "No User Input" we suggest to add an option at the sheet protection dialog allowing to sort the content. Only drawback is that you permit it per sheet and not per range. This is kind of a duplicate to bug 57091 (and apparently working like this in MSO).

(In reply to Maxim Egorushkin from comment #2)
> My use case is analyzing a CSV file by applying different sorts and filters
> to its rows and columns.
Never experienced any protercion issue with CSV. Sheet protection is enabled manually via Tools > Protect Sheet (the suggested option would be added to this dialog) and you control per cell if a protection is _not_ applied. See also the discussion on bug 143349.

> Not completely worthless, though, because such a design and functionality
> can still be used as bad examples in textbooks to contrast with the
> fundamental engineering principle of least astonishment. 
:-)

(In reply to Heiko Tietze from comment #7)
> We discussed this topic in the design meeting.
> 
> To rephrase the request, it should be possible to sort data in protected
> sheets (filtering works out of the box). Sorting may affect the data
> integrity, for example if a reference changes after sorting. We should also
> keep in mind that you can limit sort to ranges. So this option would be a
> bit dangerous, yet there might be scenarios where it is acceptable and
> needed.
> 

> > Not completely worthless, though, because such a design and functionality
> > can still be used as bad examples in textbooks to contrast with the
> > fundamental engineering principle of least astonishment. 
> :-)

Not sure if a procedure I currently use would be included as bad example but it certainly obviates the known shortcoming of ranges incorporated into formulae that are then sorted. It couldn't work over vast ranges but I find that it's adequate for smaller selections.
it'a pointless having a formula referring to a "range" which can be disrupted by a sort where those ranges tend to be relative to the current location, but if the referenced "range" is only a smaller matrix then I find that instead of something like =SUM(AA10:AA20) where the rows can end up all over the sheet - it achieves the correct objective if the formula is modified to =AA10+AA11+AA12..... or even =SUM(AA10;AA11;AA12;AA.......) where each individual cell is then tracked to its new location and therefor correctly summed.
Imagine having a few thousand lines which are really consistent daily events in clusters of 8. These clusters can be replicated and retain both their "internal" and "external" integrity so even =SUBTOTAL() works perfectly if some of the daily events are filtered out.
Not sure if I'm adding value to the conversation or just a pain in the derriere but it works for my analysis of "repeating" events closely related to each other and without calculations traversing rows.