Cell reference value to remain constant in a sort using Paste Link

[LawTrainer]

We are working with an Excel workbook and would like to use cell references
in worksheet B to reference a value from worksheet A.

However, when we sort the data in worksheet A with the value, it is moved,
and the absolute reference in Worksheet B reflects the cell reference, but
the value has been changed in the sort.

How do we set the absolute or cell reference value to remain constant but
reflect the new cell location during the sort?

I hope this examples helps to explain. We are using Excel 2003.

Thanks for your help!

Example - before sort

Worksheet A (before sort)
column A
Row 1 1
Row 2 2
Row 3 3
Row 4 4
Row 5 5

Worksheet B
column A
Row 1 ='Worksheet A'!$A$1 (value = 1)

Example - after sort

Worksheet A (after sort)
column A
Row 1 5
Row 2 4
Row 3 3
Row 4 2
Row 5 1

Worksheet B
column A
Row 1 ='Worksheet A'!$A$1 (value = 5) We would like the reference to
reflect the new location of the value "1" which should be ='Worksheet A'!$A$5.

 

[dlw]

use A1 instead of $A$1

 

[LawTrainer]

We did try the A1 without the dollar signs and it did not work. Are there
any other suggestions, such as a function?

Thanks for your help?