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


L

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.
 
Ad

Advertisements

Ad

Advertisements

L

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?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top