HOW TO: Sort Cells Without Sorting Linked Cells in Excel 2000

Article translations Article translations
Article ID: 212079 - View products that this article applies to.
This article was previously published under Q212079
Expand all | Collapse all

On This Page

SUMMARY

This step-by-step article shows you how to sort linked data without affecting cells that are linked to the original data.

If you create links to a range of cells and you sort the data to which the cells are linked, the linked cells are updated with the sorted data. This article contains an example that sorts linked data without affecting cells that are linked to the original data.

How to Sort Cells

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, enter 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, click Descending, and then 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.

REFERENCES

For more information about the OFFSET function, click Microsoft Excel Help on the Help menu, type offset in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

Properties

Article ID: 212079 - Last Review: September 27, 2003 - Revision: 3.1
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
Keywords: 
kbhowto kbhowtomaster KB212079

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com