Sorting a range that is referenced elsewhere

On sheet1, cell Sheet1.B9 content is set to: =Sheet2.B9. At this point, I want to reorder everything on sheet2 by sorting on column C. I expected the references to sheet2 on sheet1 to change, such that if sort moves sheet2.B9 to sheet2.B7, the cell on sheet1 that previously referenced the sheet2 cell would have been updated, but this did not happen. Is what I’ve expected really the way it’s supposed to work? Or is LO wroking wrong?

LO version

update: I thought I had the answer from the thread “Calc sort is broken in 5.1.0”, but after setting Tools/LibreOffice Calc/General “Update references when sorting range of cells”, it wroks the same way as my original description.

If I’m not wrong you only need to enable the option:

Menu/Tools/Options/LibreOffice calc/General - Update references when sorting range of cells.

Yes, I did that. Still doesn’t work.

I have tested before comment. Please share a sample file where to verify the issue.

I took out a bunch of sensitive stuff from the problem file, but now the references follow the sort. I’ll do some more sleuthing…


I do not reproduce on windows with Version: and ToolsLibreOffice CalcGeneralUpdate references when sorting range of cells checked.

HTH - Regards

I expected the references to sheet2 on sheet1 to change, such that if sort moves sheet2.B9 to sheet2.B7, the cell on sheet1 that previously referenced the sheet2 cell B9 would have been updated to reference Sheet2.B7 now,

Is the above quotation completed in boldface by me what you actually meant?
If so: Your expectation is one some users share and others do not. Even for a single user there may be reasons to want the references “adjusted” on sorting in one situation and not adjusted in another situation. In an extreme case we may want to make differences even concerning one specific case of sorting.

The dillema not seems solvable completely to me. There were discussions about this issue in the time of versions 4.2.8 through 4.3.4 if I remember correctly. As a result there was introduced the option ‘Update references when sorting range of cells’ in ‘LibreOffice Calc’ > ‘General’. You may play with it.

Please be aware of the fact that tools to sort data primarily are meant to sort data and not to worry about their usage. I always was satisfied with references not updated. To understand my reasons you may study the attached example where on Sheet2 the range A1:A30 is containing numbers which originally were present in the same order in B1:B30. Colored in magenta there are 4 cells of Sheet1 that are containing formulae (3 cells: one array formula, the forth one a SUM). Sheet1.B9 was as you described it. Sheet2.B1:B30 was sorted after entering the formulae with the above mentioned option active. Were the references in the magenta cells adjusted? Why not? Was the reference in cell Sheet1.B9? Can we get a consistent behaviour this way? There are many additional related questions.
My summary of this: Always set the option ‘Update references when sorting range of cells’ INACTIVE.ask65742UpdateReferencesWhenSortingRangeOfCellsRejection001.ods

I’m not sleeping tonight until I figure this out. :slight_smile: And yes to your question. I think I meant what you said you wondered about when you asked if I meant what I said.

Just slightly different opinions about the adequate precision in this case, possibly. We migth not get around the dodgy implications of ‘updating’ refrences if we do not stick to extreme precision.

It amazes me what a reboot will fix. When I had the original problem, I did NOT have the “update references when sorting” option set. After changing the option, and after numerous close/open/retries, the problem persisted with the original file, but not with my test file. After a reboot of the machine, everything works as expected. Thanks for helping me puzzle through this. (I don’t think I selected the “load LibreOffice at system boot” option on this machine when I installed LO)…

To also consider my answer and the example attached there may pay. The option ‘Update references…’ might be poisoned. In my opinion we are talking of yet another shell game.