XL: Data Sort Not Sorting Correctly in Excel

ID: Q48254

The information in this article applies to:

Microsoft Excel for Windows, versions 2.x, 3.x, 4.x, 5.0, 5.0c

SUMMARY

After you perform a data sort on a range, the range seems to have sorted and returned to its original unsorted order. (NOTE: in version 5.0, Microsoft Excel prompts you if you select a range contained within a contigious range of data.)

MORE INFORMATION

This problem occurs if the data you are sorting is composed of the results of formulas. The results may be sorted and appear sorted for a moment, but the formulas recalculate and the original results return to the cells.

To avoid this problem, make sure that the source cells for the calculations in the formulas are sorted as well.

Example


   What You See                   What Is Really There
   ------------                   --------------------
   A1: 1  B1: 2                   A1: 1  B1: =A1+1
   A2: 0  B2: 1                   A2: 0  B2: =A2+1

If you select B1:B2 and sort in ascending order, you will see the results of the formulas appear quickly in ascending order, then the formulas will recalculate the original contents of the affected cells. This assumes that the entries in column A are not included in the sort.

A second workaround for this problem is to select the cells with the formulas and then perform Edit Copy, Edit Paste Special Values. This method works only if it is acceptable to replace the formulas with the resulting values permanently.

A third workaround is to use absolute reference (example =$A$1). The following macro converts a range of cells to absolute references:


   A1: =FOR.CELL("curcell",,TRUE)
   A2: =FORMULA(FORMULA.CONVERT(GET.CELL
        (6,curcell),TRUE,FALSE,1,curcell),curcell)
   A3: =NEXT()
   A4: =RETURN()

A fourth workaround is to use the sheet name in the reference. When the sheet name is included it remains the same as an absolute reference. However, you can use relative references (example =sheet1!a1+1) so that the formula can be easily copied through the range.

Additional query words: 7.00 2.00 2.01 2.10 2.20 2.21 3.00 4.00 4.00a

Keywords :
Version : WINDOWS:2.x,3.x,4.x,5.0,5.0c,7.0
Platform : WINDOWS
Issue type :