How to sort data with off-sheet dependents?

[nomail1983]

I have a workbook with two worksheets, one called "Summary", the other
with the raw data. The "Summary" worksheet refers to cells in the "raw
data" worksheet. To my surprise, when I sorted blocks of data in the
"raw data" worksheet, the relative references in the "Summary"
worksheet were not updated.

First, is that an Excel defect, or did I do something wrong, or are my
expectations misplaced?

If it is a known defect, has it been fixed? I am using Office Excel
2003 (11.8033.8036) SP2.

Second, is there a simple way that I can set up the references in the
"Summary" worksheet so that they track movement of the cells in the
"raw data" worksheet -- especially, sorting?

(They do track simple movements, e.g. when rows and columns are
inserted, and even when cells are moved individually.)

 

[paul]

i created a little workbook made up a list and copied it,I then pasted a link
to that range(paste special paste links) and when i sort the source list the
dependent one follows it.

 

[nomail1983]

i created a little workbook made up a list and copied it,I then pasted a link
to that range(paste special paste links) and when i sort the source list the
dependent one follows it.

Please look carefully at the formulas in the pasted links. In your
simple case, it is easy to create the illusion that the cells tracked
the sort changes, but in fact they did not.

When I do the same thing as you, initially the pasted links are in the
order as the original, as expected. For example, if the original data
is the range A1:A6 on Sheet1 and I paste links into C1:C6 on Sheet2,
then in Sheet2 I see the following formulas, starting with C1:
=Sheet1!A1, =Shee1!A2,..., =Sheet1!A6. When I sort the original data
(I started with 1,2,...,6 and sorted in descending order), A6 moves to
A1, A5 moves A2,..., A1 moves to A6. Looking at Sheet2, I do indeed
see the data in C1:C6 in the same order as they appear on Sheet1,
namely 6,5,...,1. But the formulas had not changed; they are still:
=Sheet1!A1, =Sheet1!A2,..., =Sheet1!A6.

That makes a difference when, in fact, the original data is categorized
one way, and the linked data combine the original categories into
different categories. For example, in the original experiment, instead
of simply pasting links, consider the following formulas in Sheet2:

B1: Odd; C1: =Sheet1!A1 + Sheet1!A3 + Sheet1!A5
B2: Even; C2: =Sheet1!A2 + Sheet1!A4 + Sheet1!A6

In this example, C1 should be 9, and C2 should be 12. Now sort the
original data randomly by putting =RAND() into B1:B6 and sorting A1:B6
(two columns) based on column B. In my case (and in all likelihood),
C1 and C2 on Sheet2 will no longer contain 9 and 12. That is because
the formulas in C1:C2 on Sheet2 did not change to track the movement of
cells on Sheet1.

Aha! I hasten to note that we can duplicate the same experiment -- and
the same "failed" results -- by putting the "odd" and "even" formulas
on the same sheet as the original data (Sheet1).

My conclusion: Sort overwrites the cells instead of moving cells; not
an unreasonable expectation. Consequently, it "makes sense" (albeit
undesirable, IMHO) that references to the sorted cells are not changed.
In that case, I guess I am simply SOL :-(.

But if the Sort algorithm has been changed in some Office patch, I
would appreciate it if someone could let me know. Although we perform
Windows Updates periodically, we have never done an Office Update. (I
used to think that would be included in Windows Updates until a recent
posting elsewhere made it clear that it does not.)