maintaining a cell reference despite changing the cell location through sorting

BerlinerTourGuide 

If you are looking for say the second largest value of a group, there is the LARGE function.

https://www.excelchamp.net/finding-the-second-largest-value-in-excel/#:~:text=%3DLARGE(A1%3AA5%2C,largest%20value%20from%20the%20array.

But, the fail-safe way is to use INDIRECT, example =INDIRECT("A1"), as noted here:

https://superuser.com/questions/1390320/is-there-a-way-to-absolute-the-referred-or-target-cell

BerlinerTourGuide 

I can.t help thinking that the problem stems from the 'quick and dirty' philosophy that pervades spreadsheet practice (though it is responsible for its huge success in terms of adoption).  The idea of clicking on a cell to reference it by location (A1 etc.) on the sheet means that the user action is incorporated into the model but their intent is not evident. 

In the present case, the data might be a list or, less likely, an array.  If it were an array, then order within the array is significant (data is access by an index or a combination of indices) and on no account should the order be changed by sorting.  Preventing sorting or at least identifying it as an error is more important than coping with it.

Assuming your data is actually a list, then sorting is an inherent property of a list and direct references to other records by location should not be used because the property that is not conserved under a sort operation.  Reference is normally performed through the use of primary/foreign key matches which pick up the required data irrespective of its location.  If, as you suggest, there is a requirement to aggregate a block of values, then members of the block need to be identifiable from their attributes.  SUMIFS will pick up the correct values irrespective of their sort order.

In summary, you get what you pay for.  Cheap and nasty shortcuts get the initial job done fast but can easily go wrong … which might go a long way towards explaining the fact that 3rd-party audits have shown that fewer than 1 in 10 spreadsheets are actually correct.

Note:  As an aside, if you are processing lists, ensure that they are held as Tables.  If it is simply a range, then it is only too easy to sort some of the fields whilst leaving others in their original order.  This destroys the integrity of the data and is irrecoverable once options for 'Undo' have been exhausted.

BerlinerTourGuide ,

The workaround with the reference could be organize as

(assuming your data is in column A starting from cell A1 and you reference on the value of the cell in 15th position, other words on A15 in unfiltered range)

=INDEX($A:$A,
  COUNTA($A$1:INDEX($A:$A,ROW(A15)-ROW($A$1)+1)) -
  SUBTOTAL(103,$A$1:INDEX($A:$A,ROW(A15)-ROW($A$1)+1)) +
  ROW(A15)-ROW($A$1)+1
)

Not very practical...