Microsoft KB Archive/298585

= XL2001: Range Returns to Unsorted Order When You Perform a Data Sort on a Range =

Article ID: 298585

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Excel 2001 for Mac
 * Microsoft Office 98 for Macintosh

-



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(&quot;curcell&quot;,,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.

Additional query words: OFF2001 mac OFF2001KB XL2001 XL2001KB XL excel sort filter manage change modify query incorrect wrong different

Keywords: kbprb KB298585

-

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

© Microsoft Corporation. All rights reserved.