How to Sort Linked Sheets in Excel Without Screwing up the Linked Data

Excel is a super tool! Of course, being of the finance profession, I guess I'm a bit biased as I simply could not function without Excel. A fellow eHow member requested this article and here's my shot at it ...

Instructions

Difficulty: Moderately Easy

Things You’ll Need:

• Excel, of course!

1. In Excel, links are used to pull data from other locations either within the same spreadsheet or workbook or from a separate spreadsheet. Linked cells returns the exact value from a specified location as in the illustrated example below. The price of bananas shows as $2.47, but the cell actually contains a formula referencing the location of the original data: =’Fruit Prices!’B2
   
   Excel is set up so that anytime you move a formula, by copying & pasting, the linked formula changes … this is called a relative reference. If you copy cell B2 and paste it three cells over to the right, the formula will move the referenced location three cells over to the right also. This is why sorting will change the location of the linked cell.
2. To keep formulas from changing as they are moved around, you have to anchor the cell reference by placing a dollar sign in front of both the column reference and the row reference. Instead of =’Fruit Prices!’B2, an anchored reference should look like =’Fruit Prices!’$B$2. No matter where the linked cell moves, the formula will reference that exact location … this is called an absolute reference.
3. Unfortunately there is not an simple and easy way to change relative references to absolute references. If a set of cell formulas share a row or column in common, you can use edit and replace to add dollar signs. See example below.

Tips & Warnings

• I might would suggest creating a PivotTable as an alternative to sorting linked data
• Check mrexcel.com for questions pertaining to spreadsheets