Microsoft KB Archive/40401

= XL: Errors Sorting Cells That Contain References =

Article ID: 40401

Article Last Modified on 7/19/2007

-

APPLIES TO


 * 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

-



This article was previously published under Q40401



SYMPTOMS
In Microsoft Excel, when you sort a column of values that are the result of arithmetic operations based on relative references, you may receive #REF error values.



CAUSE
To correctly sort a range that includes cells with references, all references must be absolute references. Using relative references can result in errors because the references may become invalid when the cells are rearranged in the specified sort order.

For example, given these cells and sorting A1:C3 in ascending order with 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 B3 has lost its reference to A3 and now refers to A5. The formulas in column C have been correctly sorted.



MORE INFORMATION
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

Additional query words: data

Keywords: kbprb KB40401

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.