How to avoid cell reference changes after sorting in Excel

[Wisaal]

I am having similar difficulty. Relative cell reference act like an absolute reference. When the precedent worksheet is sorted the descendant worksheet reflects the cell reference prior to the sort. My experience has been that by using relative reference formats =A1 the cell reference would change with a sort in the precedent worksheet, always giving the same result in the descendant worksheet. Using absolute reference would force the data to always reflect what was in the cell referenced. If 10 was in cell A1 and a sort moved it to A99, the number 10 would still be reported in the linked cell. Using absolute reference =$A$1, the sort would report whatever data was moved
into the =$A$1 cell. Never had this happen before and have yet to resolve. i'm wondering if there is a global setting that lock the spreedsheet or if various format setting cause this problem.

[Alfons]

In this case it does not matter whether you use a relative or absolute reference. Sorting will not change the reference in either case. The point is that if a formula refers to the row in which it stands. When I search now, and the line as now no longer in line 5 is 10, but would have the formula now refer to line 5. And that's just not for me. Sort by is still the old-line reference in the formula, thus providing incorrect results.

[Gania]

What would be possible is that each line is given a unique identifier - in Access is the key. With this key can then be "resorted" to the Excel sorting in all members of the other columns. But this approach requires a not inconsiderable (VBA) effort, and I do not know if this is justified.