Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference value to remain constant in a sort using Paste Link
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. -- Lawtrainer |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference value to remain constant in a sort using Paste Link
use A1 instead of $A$1
"LawTrainer" wrote: 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. -- Lawtrainer |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference value to remain constant in a sort using Paste
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? -- LawTrainer "dlw" wrote: use A1 instead of $A$1 "LawTrainer" wrote: 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. -- Lawtrainer |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I set up a constant reference to a cell that moves? | Excel Worksheet Functions | |||
excel uses what technique to keep a cell reference constant when | Excel Discussion (Misc queries) | |||
How to make a cell address remain constant when copying a formula | Excel Worksheet Functions | |||
remain constant in a copy & paste | New Users to Excel | |||
data source remain constant | Charts and Charting in Excel |