Preservation of references after sort - is there a spreadsheet application, or an option to do this?

Question

If you have references to individual cells in a table, they are destroyed if you sort the table. I was wondering if there was a spreadsheet application that allowed you to override this behaviour.

The same behaviour as that below occurs in Libre Office, OpenOffice, and Numbers. It would be good to know of an option in any of these, or any spreadsheet program (excluding Windows ones), that have an option to change this.

For Example:

    A           B       C
1   Name        Number  Count
2   The         100     500
3   Quick       200     400
4   Brown       300     350
5   Fox         400     300
6   Jumps       500     250
7   Over        600     200
8   The         700     150
9   Lazy        800     100
10   Dog        900     50
11   Total      4500    2300
12   Even       2000    850
13   Odd        1600    1450

Where:

B12 = SUM(B9;B7;B5;B3) = 2000

If you sort the above table, by column A, you get:

B12 = SUM(B9;B7;B5;B3) = 2100

What is actually wanted is for the sort to give:

B12 = SUM(B6;B7;B4;B8) = 2000   

Using the 'Indirect()' function, that is mentioned in some of the answers to this question, doesn't change this behaviour.

What is needed is a sort function that updates all references, so that, if location B3 -> B8 in the table, the same transformation is made if B3 is referenced anywhere else.

Answer 1

I've found the solution, for LibreOffice. It's necessary to turn on the option to 'update references on sort, which is found in 'preferences'. I've tested this option, and it works, the references are updated properly.

I have not been able to find a way to do this in Numbers, nor in OpenOffice.

This link may be useful to anybody interested in the situation with LibreOffice - it explains how they fixed the problem, then reversed the fix after complaints that old spreadsheets broke:

https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=74986