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
And yes to your question. I think I meant what you said you wondered about when you asked if I meant what I said.