Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting formula cells not same as sort of value cells? Yikes!
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??? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting formula cells not same as sort of value cells? Yikes!
On Oct 13, 12:00*pm, "Barn E. Fife" wrote:
Surely this is not by design??? 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting formula cells not same as sort of value cells? Yikes!
"Barn E. Fife" wrote:
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. 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting formula cells not same as sort of value cells? Yikes!
On Oct 13, 1:00*pm, "Barn E. Fife" wrote:
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??? 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting formula cells not same as sort of value cells? Yikes!
On Oct 13, 1:23*pm, "joeu2004" wrote:
If you highlight and sort C1:C2 ascending, it does not sort as desired. Change the formulas to use A$1 and A$2 (or $A$1 and $A$2), and it will work as you expect. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting blank cells with a formula in them | Excel Discussion (Misc queries) | |||
Yikes! Merged cells making life miserable. | Excel Discussion (Misc queries) | |||
Formula for sorting a range of cells in decending order | Excel Discussion (Misc queries) | |||
I can't sort data when the key column cells contain formula | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |