Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting formulas to stick during sorting
Hello,
I'm using Excel 2003. I would like to be able to sort columns A-F, but I would like the formulas in column D to remain unsorted. Here is what I have for the formulas in column D: Sheet1 Column D Formulas (before sort) ------------------------------- row1: =If('Sheet2'!$AA$1<"", 'Sheet2'!$AA$1,"") row2: =If('Sheet2'!$AA$2<"", 'Sheet2'!$AA$2,"") row3: =If('Sheet2'!$AA$3<"", 'Sheet2'!$AA$3,"") row4: =If('Sheet2'!$AA$4<"", 'Sheet2'!$AA$4,"") etc No matter what sorting takes place in rows A-F, I would like the formulas in Column D to remain as above (in "chronological" order, so to speak). The problem is, after I sort my data, Column D is sorted with A-F, and I end up with formulas in Column D looking something like this: Sheet1 Column D Formulas (after sort) ------------------------------- row1: =If('Sheet2'!$AA$19<"", 'Sheet2'!$AA$19,"") row2: =If('Sheet2'!$AA$31<"", 'Sheet2'!$AA$31,"") row3: =If('Sheet2'!$AA$7<"", 'Sheet2'!$AA$7,"") row4: =If('Sheet2'!$AA$2<"", 'Sheet2'!$AA$2,"") etc ....or whatever resulting order the sorting on rows A-F dictated. Moving Column D outside of A-F is not an option. I also learned that sorting with multiple selctions is not allowed. Any ideas? Thanks, Scott |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting formulas to stick during sorting
yes, i have an idea - use a macro to sort a-c
and then sort e-f. i'm thinking, though, that you're going to say it won't work because you're using a value in e-f to be the key to the sort a-c, or vice versa. so..... you could have a macro that sorts the whole darn thing, and then inserts =If('Sheet2'!$AA$1<"", 'Sheet2'!$AA$1,"") in d1 & autofills it down as far as your rows go. somebody else may have a simpler/more clever way to do it. :) susan On Jul 27, 12:46 pm, scottydel wrote: Hello, I'm using Excel 2003. I would like to be able to sort columns A-F, but I would like the formulas in column D to remain unsorted. Here is what I have for the formulas in column D: Sheet1 Column D Formulas (before sort) ------------------------------- row1: =If('Sheet2'!$AA$1<"", 'Sheet2'!$AA$1,"") row2: =If('Sheet2'!$AA$2<"", 'Sheet2'!$AA$2,"") row3: =If('Sheet2'!$AA$3<"", 'Sheet2'!$AA$3,"") row4: =If('Sheet2'!$AA$4<"", 'Sheet2'!$AA$4,"") etc No matter what sorting takes place in rows A-F, I would like the formulas in Column D to remain as above (in "chronological" order, so to speak). The problem is, after I sort my data, Column D is sorted with A-F, and I end up with formulas in Column D looking something like this: Sheet1 Column D Formulas (after sort) ------------------------------- row1: =If('Sheet2'!$AA$19<"", 'Sheet2'!$AA$19,"") row2: =If('Sheet2'!$AA$31<"", 'Sheet2'!$AA$31,"") row3: =If('Sheet2'!$AA$7<"", 'Sheet2'!$AA$7,"") row4: =If('Sheet2'!$AA$2<"", 'Sheet2'!$AA$2,"") etc ...or whatever resulting order the sorting on rows A-F dictated. Moving Column D outside of A-F is not an option. I also learned that sorting with multiple selctions is not allowed. Any ideas? Thanks, Scott |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting Values Without Sorting Formulas | Excel Discussion (Misc queries) | |||
Sorting Formulas | Excel Discussion (Misc queries) | |||
Sorting with formulas | New Users to Excel | |||
Row Stick | Excel Discussion (Misc queries) | |||
Sorting and having cell formats stick with contents | Excel Discussion (Misc queries) |