Value used in a Paste Link to remain constant during a sort

[LawTrainer]

We are working with an Excel worksheet that uses absolute cell references in
the worksheets using the Paste Link feature.

However, when we sort the data in Worksheet A, the absolute cell reference
in Worksheet B reflects the change in data (see example below).

We would like for the value in the cell reference to remain constant and
reflect the new cell reference location from the sort. How do we format the
formula to reflect this? I've tried INDIRECT and CELL functions and it
didn't work.

We are using Excel 2003. Thanks for your help!

Example (Before Sort)

Worksheet A
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
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 for the value to
remain constant an the reference to follow the new location of the value = 1
which should be =Workdsheet A'!$A$5.

Thanks, hope this example helps to explain what we are looking for.

 

[Héctor Miguel]

hi, !

We are working with an Excel worksheet that uses absolute cell references
in the worksheets using the Paste Link feature.
However, when we sort the data in Worksheet A, the absolute cell reference
in Worksheet B reflects the change in data (see example below).
We would like for the value in the cell reference to remain constant
and reflect the new cell reference location from the sort.
How do we format the formula to reflect this? I've tried INDIRECT and CELL functions and it didn't work.
We are using Excel 2003. Thanks for your help!

AFAIK, this is the "normal" behavior when you sort data-lists
- cell references remains the same (but...)
- cell's contents are (re)written in the data-list (so,)
- cell references shows it's "new data"
- (also) formulae/functions within the data-list becomes a messed results -?-

one (possible) way is NOT using paste-link feature...
but functions like vlookup(..., index(... & match&..., and so on...

hth,
hector.

__ OP __