Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data with off-sheet dependents?
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.) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to sort data with off-sheet dependents?
paul wrote:
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.) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting data on protected worksheet | Excel Discussion (Misc queries) | |||
Want cell ref. to change after sort in other sheet | Excel Discussion (Misc queries) | |||
Help to sort out filtered data from the data contained in another sheet of the same workbook | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
Sort data from 1 work sheet into another | Excel Worksheet Functions |