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

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