Weird Sorting Behavior
 

I have run across some odd sorting behavior. Before I call it a bug I would
like to find out if I am missing something. Sorting seems to function
incorrectly if you reference the sheet in the formula.
Here is the sheet that I set up to demonstrate the problem...

Columns A thorugh C are just constants something like this
A B C
1 10 1
2 9 2
3 8 3
4 7 4
5 6 5
6 5 6
7 4 7
8 3 8
9 2 9
10 1 10

Columns D and E are formulas like this
A + B * C A + B * C
=(A2+B2)*C2 =(Sheet1!A2+Sheet1!B2)*Sheet1!C2
=(A3+B3)*C3 =(Sheet1!A3+Sheet1!B3)*Sheet1!C3
=(A4+B4)*C4 =(Sheet1!A4+Sheet1!B4)*Sheet1!C4
=(A5+B5)*C5 =(Sheet1!A5+Sheet1!B5)*Sheet1!C5
=(A6+B6)*C6 =(Sheet1!A6+Sheet1!B6)*Sheet1!C6
=(A7+B7)*C7 =(Sheet1!A7+Sheet1!B7)*Sheet1!C7
=(A8+B8)*C8 =(Sheet1!A8+Sheet1!B8)*Sheet1!C8
=(A9+B9)*C9 =(Sheet1!A9+Sheet1!B9)*Sheet1!C9
=(A10+B10)*C10 =(Sheet1!A10+Sheet1!B10)*Sheet1!C10
=(A11+B11)*C11 =(Sheet1!A11+Sheet1!B11)*Sheet1!C11

Columns D and E are identical formulas except that E references the sheet it
is on. so my final data looks like this.

A B C A + B * C A + B * C
1 10 1 11 11
2 9 2 22 22
3 8 3 33 33
4 7 4 44 44
5 6 5 55 55
6 5 6 66 66
7 4 7 77 77
8 3 8 88 88
9 2 9 99 99
10 1 10 110 110

If you sort on Column D all is well and everything behaves as normal. If you
sort on column E Ascending (AtoZ)nothing happens no matter how many time you
click the button. When you sort on Column E descending (ZtoA)the data resorts
itself. Sort descending again and it resorts again. Every click resorts the
data back and forth. If you sort on columns A through C the Formulas in
Column E move around.

Any bright ideas what is happening here.

--
Thanks In Advance...

Jim Thomlinson

Weird Sorting Behavior
 

Sorts don't play nice with formulas that refer to cells on another worksheet.

I don't see anything in your post that would help, but when I have this kind of
stuff, I try to put a key (unique) value on both sheets, then use =vlookup() to
return the values I want.

Jim Thomlinson wrote:

I have run across some odd sorting behavior. Before I call it a bug I would
like to find out if I am missing something. Sorting seems to function
incorrectly if you reference the sheet in the formula.
Here is the sheet that I set up to demonstrate the problem...

Columns A thorugh C are just constants something like this
A B C
1 10 1
2 9 2
3 8 3
4 7 4
5 6 5
6 5 6
7 4 7
8 3 8
9 2 9
10 1 10

Columns D and E are formulas like this
A + B * C A + B * C
=(A2+B2)*C2 =(Sheet1!A2+Sheet1!B2)*Sheet1!C2
=(A3+B3)*C3 =(Sheet1!A3+Sheet1!B3)*Sheet1!C3
=(A4+B4)*C4 =(Sheet1!A4+Sheet1!B4)*Sheet1!C4
=(A5+B5)*C5 =(Sheet1!A5+Sheet1!B5)*Sheet1!C5
=(A6+B6)*C6 =(Sheet1!A6+Sheet1!B6)*Sheet1!C6
=(A7+B7)*C7 =(Sheet1!A7+Sheet1!B7)*Sheet1!C7
=(A8+B8)*C8 =(Sheet1!A8+Sheet1!B8)*Sheet1!C8
=(A9+B9)*C9 =(Sheet1!A9+Sheet1!B9)*Sheet1!C9
=(A10+B10)*C10 =(Sheet1!A10+Sheet1!B10)*Sheet1!C10
=(A11+B11)*C11 =(Sheet1!A11+Sheet1!B11)*Sheet1!C11

Columns D and E are identical formulas except that E references the sheet it
is on. so my final data looks like this.

A B C A + B * C A + B * C
1 10 1 11 11
2 9 2 22 22
3 8 3 33 33
4 7 4 44 44
5 6 5 55 55
6 5 6 66 66
7 4 7 77 77
8 3 8 88 88
9 2 9 99 99
10 1 10 110 110

If you sort on Column D all is well and everything behaves as normal. If you
sort on column E Ascending (AtoZ)nothing happens no matter how many time you
click the button. When you sort on Column E descending (ZtoA)the data resorts
itself. Sort descending again and it resorts again. Every click resorts the
data back and forth. If you sort on columns A through C the Formulas in
Column E move around.

Any bright ideas what is happening here.

--
Thanks In Advance...

Jim Thomlinson

--

Dave Peterson