Getting formulas to stick during sorting

[scottydel]

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

 

[Susan]

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