XL: Errors Sorting Cells That Contain References

This article was previously published under Q40401
This article has been archived. It is offered "as is" and will no longer be updated.
In Microsoft Excel, when you sort a column of values that are the result ofarithmetic operations based on relative references, you may receive #REFerror values.
To correctly sort a range that includes cells with references, allreferences must be absolute references. Using relative references canresult in errors because the references may become invalid when the cellsare rearranged in the specified sort order.

For example, given these cells and sorting A1:C3 in ascending orderwith the sort key being cell A1
   A1: 3  B1: =A3     C1: =$A$3   A2: 2  B2: =A2     C2: =$A$2   A3: 1  B3: =A1     C3: =$A$1				
the resulting formulas are:
   A1: 1  B1: =#REF!  C1: =$A$1   A2: 2  B2: =A2     C2: =$A$2   A3: 3  B3: =A5     C3: =$A$3				
Note that cell B1 is now an invalid reference and that cell B3has lost its reference to A3 and now refers to A5. The formulasin column C have been correctly sorted.
For additional information, please click the article numbers below to view the articles in the Microsoft Knowledge Base:
70096 XL: Using FORMULA.CONVERT() to Change Relative/Absolute References

Article ID: 40401 - Last Review: 08/23/2013 17:21:02 - Revision: 1.3

  • Microsoft Excel 2.0 Standard Edition
  • Microsoft Excel 2.01
  • Microsoft Excel 2.1 Standard Edition
  • Microsoft Excel 2.10c
  • Microsoft Excel 2.10d
  • Microsoft Excel 3.0 Standard Edition
  • Microsoft Excel 3.0a
  • Microsoft Excel 4.0 Standard Edition
  • Microsoft Excel 4.0a
  • Microsoft Excel 4.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 5.0c
  • Microsoft Excel 5.0 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • kbnosurvey kbarchive kbprb KB40401