Data -> Sort not updating cell references

[Basinator]

Data -> "Sort" doesn't work with cells containing results. The cell's cell references aren't updated after sorting.
Thus, the "result cells" aren't containing the true result, but after manually changing the cell references to the correct cells the sorting actually *is* done correctly.

This happened on one computer, but not at the other (same LO version). Any clue why?

OS: Linux (Ubuntu + Mint)

Example:
(results are done by cell references)
addition of numbers;
line 1: 6 + 8 = 14
2: 5 + 7 = 12
3: 2 + 8 = 10

If I would sort reverse-wise here, it would result in the following:
1: 2 + 8 = 14
2: 5 + 7 = 12
3: 6 + 8 = 10

This is because the cell references aren't changing to the line moving along the result. However, the results ITSELF, after being updated with the correct cell references, are sorted correctly:
10
12
14

[MrProgrammer]

[Solved] Sorting on the results of formulas
Sort does not work in sheets with vertical cell-references

You are more likely to get specific help if you attach a document demonstrating the difficulty (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself). Explain each step needed to reproduce the problem.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Basinator]

Yeah, this is exactly my problem. What I don't get why it seems to work on one machine but not on the other despite them having the same LO version. Maybe the ubuntu-Devs did fix such an issue and compiled it for their packet servers?

Is there a software-side fix for this? To be honest, using multiple other sheets and formulaes seems to be rather complicated.

[gerard24]

What I don't get why it seems to work on one machine but not on the other despite them having the same LO version

It we be easier to answer if we know what version of LibreOffice you have.

[Basinator]

Version: 4.2.7.2
Build-ID: 420m0(Build:2)

[gerard24]

Version: 4.2.7.2
Build-ID: 420m0(Build:2)

Usual suspect. 4.2.7 is buggy on sorting with formula.
http://www.reddit.com/r/linux/comments/ ... y_bug_may/
Upgrade to newest version.

[Basinator]

Does 4.2.8 solve this?
Is 4.3 stable?

[gerard24]

Does 4.2.8 solve this?

Yes.

Is 4.3 stable?

More or less..
There is no more bugs than in 4.2.8.

[Lupp]

@Basinator - Don't blunder into that trap!
The question if references should be "adjusted" on sort (and this by default or ...) was discussed a lot last year in the libreoffice.org/bugzilla universe.
In very short:
The 'adjust_references_on_sort' behaviour was introduced silently as default with V4.2.7 (or .8?) of LibreOffice.
As should have been expected there came "objections" (not too timidly put in words) in the ask.libreoffice.org forum, in the libreofficeforum.org and posted as bug reports, too.
These from users having lost the functionality of sheets they had designed relying on the former behaviour.
A slightly confusing discussion arose, including some persons near to developers or developers themselves.
The "enhancement" was finally accepted to be a bug. Release of version 4.3.4 was accelerated to get rid of it as soon as possible.

Please don't start a back-and-forth about the topic. Understanding the implications of the "adjust_on_sort_question" seems not to be really easy. Even introducing a new user setting for the purpose might cause a lot more of "need for help" in the forums than it can possibly enhance usage.

[Lupp]

(Appendix to the post above)
Starting with LibreOffice 4.4.0 there is a new option 'Update references when sorting range of cells' under 'Tools' > 'Options' > 'LibreOffice Calc' > 'General'.
In my opinion it is not a good idea to have a global option for this. If needed at all, the option should be set for the individual sort descriptor. The option might also need to go into more detail regarding the question whether or not cells containing a reference under treatment are placed in the sorted range themselves. If your experiences allow discussing this based on actual examples I am interested in your opinion, as I intend to file a feature request with respect to this matter some day.

[fustbariclation]

I'm pleased to have found this old discussion it is very useful.

The old default behaviour, presumably from Excel, allowing spreadsheets using references to list items to break on sort was a most unwelcome surprise to me.

I'm delighted with the option 'update references when sorting range of cells' found in LibreOffice.

I'm running OpenOffice with 4.1.4 and can't find the option - is it there somewhere?

[Lupp]

AOO never implemented that option. I hope you don't feel too safe with it in LibO. There are reasons for what the introduction of that behaviour under sorting as the default was heavily critisised by te community and lead to long discussions in their bug-tracker. The result then was that the previous behaviour was re-established as the default, and the new option was created.

https://ask.libreoffice.org/en/question ... elsewhere/
https://bugs.documentfoundation.org/sho ... i?id=81633
https://bugs.documentfoundation.org/sho ... i?id=81309
https://bugs.documentfoundation.org/sho ... i?id=45146
...

There was a more recent discussion in the ask.libreoffice.org, but I canot find it at the moment in their badly structured heap.

[fustbariclation]

My question was whether OpenOffice can be set the same way.

Can you do anything to ensure that, when you sort a table, all references to cells in the table are updated?

That is the behaviour I expect, and the one I would like as a default for all the spreadsheet applications that I use.

[Lupp]

My question was whether OpenOffice can be set the same way.

Yes, of course. I can read.
My answer was

AOO never implemented that option.

and you surely also can read. Thus I do not undersatand your question

Can you do anything to ensure that, when you sort a table, all references to cells in the table are updated?

because the answer was already clear: AOO does not "update" (adjust) any references when cell ranges got sorted.

That {the adjustment; Lupp} is the behaviour I expect, and the one I would like as a default for all the spreadsheet applications that I use.

Your expectations are yours. The thorough discussions I mentioned which partly took place in the LibO bugtracker (partly, of course, elsewhere) resulted in the situation we have in LibreOffice Calc. I feel sure nobody among the developers would consider to change the default again and to restart the discussion by that. For AOO this seems not to be a topic.

[fustbariclation]

I asked again because I didn’t understand your reply. Thank you for the clarification.

I understand that some people don’t mind their references to cells changing on a sort.

What I don’t understand is not having an option to prevent that for people who find that behaviour perverse.

How can the option hurt? If someone doesn’t like it, he doesn’t have to use it.

Some people don’t like using seatbelts. Fine. It is their life. I don’t see that as a reason to remove them from all cars.

[MrProgrammer]

I understand that some people don’t mind their references to cells changing on a sort.

There is further discussion about why OpenOffice cannot adjust of references during Data → Sort in Formulas Don't Adapt to Sorting Their Reference Cells.

[fustbariclation]

That is a lack of imagination on the part of the developers.

It is easy to ensure that all references to a table are updated when the table is sorted, since some don’t want this, it makes sense for it to be an option.

If a function breaks on a sort as a consequence, that function will be highlighted, so the user can undo the sort and fix it.

The problem with the current situation is that things break silently.

That is dangerous behaviour in a spreadsheet.