How to keep same cell reference

[pdizzy]

What function do I need to use to keep the value of a specific cell on another sheet? I have tried =ALL!$A$5 and =ALL!A5 but if the data on the sheet ALL is sorted the =ALL!A5, etc follows the original cell. I need to only refer to cell A5 on the work sheet ALL.

[samba_ravi]

Attach a sample sheet with expected result

[cbatrody]

Hi,

Use

=INDIRECT("ALL!"&"A5")