XL98: How to Sort Cells Without Sorting Linked Cells

MORE INFORMATION

In the following example, the formulas in column D are linked to cells in columns A and B. When you sort the data in columns A and B, the formulas in column D are not affected. To use this example, follow these steps:

1. On a new worksheet, type the following values:

         A1: 10     B1: 0     C1:     D1: =Offset($A$1,B1,0)
         A2: 20     B2: 1     C2:     D2: =Offset($A$1,B2,0)
         A3: 30     B3: 2     C3:     D3: =Offset($A$1,B3,0)
         A4: 40     B4: 3     C4:     D4: =Offset($A$1,B4,0)
   						

2. Select cells A1:B4.
3. On the Data menu, click Sort.
4. Click Column A in the Sort By box, and then click Descending. Click OK.

The linked cells remain linked to the data as it appeared in the original order even after you sort the data in columns A and B.

NOTE: You must select both column A and column B to sort the list in the example. The formula uses column B as the original offset row number.