Comment 99 for bug 1389858

(In reply to Kohei Yoshida from comment #7)
> Our best option would be to make it configurable. Any attempt to
> automatically figure out when to and not to adjust would only make the
> situation worse, because unless we add a Google-level clever AI to do the
> guessing, we would never get it right, and there would always be some users
> with extreme corner cases coming out of the woodwork shouting "you broke my
> workflow!".

One thing that baffles me with the new sort is that it changes cells outside of the selected sort range.

If I wanted the sort to change the cells, for instance keep references to rows, I'd include those columns in the sort selection.

Would it help if the concept was that only cells in the sort selection should be changed? (It would at least help me a bunch and I think it would offer a workaround for those that expects cells to change all over the place).

You could also add a little checkbox in the sort dialog that said something like "Enable Hollistic Sorting" :D

Another clue (at least in my case) to when I don't want calculations to change with sort is when I have one or several columns of values and then one or several columns of formulas referencing the value-columns. And these formulas are identical part from the references that all uniformly address the same row as the formula cell or the same number of rows above/below the formula cell - I.e. uniformly referenced formulas

However a better option in this case is to insert an an empty column between the "data" cells and the "formula" cells and in that way keep sorting from selecting the formula cells when sorting, and as per above then keep sorting from changing the references in the formula cells.

/E