XL2001: Range Returns to Unsorted Order When You Perform a Data Sort on a Range (298585)


The information in this article applies to:

  • Microsoft Excel 2001 for Mac
  • Microsoft Office 98 Macintosh Edition
This article was previously published under Q298585

SYMPTOMS

After you perform a data sort on a range, the range appears to be sorted but then returns to its original unsorted order.

CAUSE

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

WORKAROUND

To resolve this issue, use one of the following methods.

Method 1

Make sure that the source cells for the calculations in the formulas are also sorted.

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 may see the results of the formulas appear quickly in ascending order, but then the formulas recalculate the original contents of the affected cells. This is because the source data entries in Column A are not included in the sort.

Method 2

Use this method if you want to replace the formulas with the resulting values permanently.
  1. Select the cells with the formulas, and then click Copy on the Edit menu.
  2. On the Edit menu, click Paste Special.
  3. On the Paste Special menu, click Values.
This replaces formulas with numbers for correct sorting.

Method 3

Use a macro to create an absolute reference (for example, =$A$1).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

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()

Method 4

Use the sheet name in the cell reference. When the sheet name is included, it remains the same as an absolute reference. However, you can use relative references (for example, = sheet1!a1+1), so that the formula can be easily copied throughout the range.
Modification Type:MajorLast Reviewed:6/23/2005
Keywords:kbprb KB298585
©2004 Microsoft Corporation. All rights reserved.