Keeping range names attached after Data, Sort?

  • Thread starter Thread starter Fred Holmes
  • Start date Start date
F

Fred Holmes

Excel 2000.

It's been my experience that when the Data, Sort command is used,
range names remain "absolute" e.g., if cell C3 has a name, that name
remains designating cell C3 after the sort has been performed, rather
than moving with the sort and designating the cell in column C of the
row to which the data originally in row 3 has been moved.

Is there any way to define a range name to a [single] cell so that it
continues to point to the cell with the same data after a Data, Sort.
I don't find this described anywhere, and things I've tried haven't
worked.

Thanks,

Fred Holmes
 
Fred Holmes said:
Excel 2000.

It's been my experience that when the Data, Sort command is used,
range names remain "absolute" e.g., if cell C3 has a name, that name
remains designating cell C3 after the sort has been performed, rather
than moving with the sort and designating the cell in column C of the
row to which the data originally in row 3 has been moved.

Is there any way to define a range name to a [single] cell so that it
continues to point to the cell with the same data after a Data, Sort.
I don't find this described anywhere, and things I've tried haven't
worked.

Thanks,

Fred Holmes
Click to expand...

When you sort, the cells themselves remain where they are; it's the contents
(i.e. the data) that move to different cells. This is quite different from
sorting the cells themselves. (You can compare this with the manual
equivelent of copying/pasting data as compared with moving cells.)
Therefore, any name will remain with the cell and not move with the data.
Formulas, whether based on names or cell references, do not adjust as a
result of sorting. That's the way it has been designed.

If you want to make things change when you sort, you have to write formulas
accordingly. For example, MATCH (set to find an exact match) will find where
a cell with a particular value is before and after sorting.
 
Back
Top