Forum Discussion
maintaining a cell reference despite changing the cell location through sorting
if the position of a value is changed through a sorting procedure the reference to the value in another cell is lost... e.g.: I reference cell A1 filled with the value 1 to which I reference in anoth...
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