Sorting formula cells not same as sort of value cells? Yikes!

  • Thread starter Thread starter Barn E. Fife
  • Start date Start date
B

Barn E. Fife

Type the letter b in A1, the letter a in A2.
C1=A1
C2=A2

Now if you highlight and sort A1:A2 ascending, it is alphabetized.
Undo it if you've done this.
If you highlight and sort C1:C2 ascending, it does not sort as
desired.

This is in XL2003. I assume that you reproduce it; thanks for
reporting that you don't.
But if you do - this is shocking. In sick irony this is a case where
hope I have a corrupt file. Surely this is not by design???
 
Surely this is not by design???
Click to expand...

I see what's likely happening; it sorts the .Value contents, but then
if preserves the referencing. C2 gets sorted to C1, but it doesn't
"move" C2=A2 to C1=A2; it sets C1=A1 preserving relative referencing.
I.e. it "copies rather than moves."

So I get that this is by design. But this is such, such an easy trap
while devastating. What rule of thumb "element of programming style"
am I breaking, or should I incorporate, to prevent this tragedy from
striking in the future? Maybe, make a rule to only sort the lowest
"source" level of data? (But then what if it's a linked read-only
file?)

Moreover, if there is a slicker approach than the clunky pasting by
value before sorting, I'm open. Thanks.
 
Barn E. Fife said:
Type the letter b in A1, the letter a in A2.
C1=A1
C2=A2 [....]
If you highlight and sort C1:C2 ascending,
it does not sort as desired.
Click to expand...

Change the formulas to use A$1 and A$2 (or $A$1 and $A$2), and it will work
as you expect.

Another example where the sort order is not changed, not surprisingly: if
C1 and C2 contain the formula =$A$1:$A$2. That formula might look odd to
you. But it is the same functionality that we rely on if we define a named
range like colA with the reference =Sheet1!$A$1:$A$2.
 
Type the letter b in A1, the letter a in A2.
C1=A1
C2=A2

Now if you highlight and sort A1:A2 ascending, it is alphabetized.
Undo it if you've done this.
If you highlight and sort C1:C2 ascending, it does not sort as
desired.

This is in XL2003. I assume that you reproduce it; thanks for
reporting that you don't.
But if you do - this is shocking. In sick irony this is a case where
hope I have a corrupt file. Surely this is not by design???
Click to expand...

When you sort cells containing formulas, you are actually moving the
formulas up and down. Because the formulas re-adjust the results are
unexpected. You can get your desired results as follows:

In C1 put =$A$1
and in C2 put =$A$2
 
Change the formulas to use A$1 and A$2 (or $A$1 and $A$2), and it will work
as you expect.
Click to expand...
The way I would accomplish that is by copying =A1 down and doing a
search and replace
A -> A$
over the range. While clunkier than copying by value, I like that it
would then be self-maintaining; if the "source" data ever changed
(i.e. column A in my example), I could sort with no additional work.
Excellent suggestion.
 
Back
Top