Running Sun's version of OOo3.1, I changed the description values in the above "Sample.xls" to ...
- Code: Select all Expand viewCollapse view
one
two
three
four
five
six
... so I have better control which text is sorted with which number.
I click any cell in the list's first column and quick-sort [A-Z]:
- Code: Select all Expand viewCollapse view
Description Amount
five $5.00
four $4.00
one $1.00
six $6.00
three $3.00
two $2.00
It correctly sorts the first column alphabetically, detecting the first header row.
Hitting the [Z-A] button, I get the exact reverse order.
- Code: Select all Expand viewCollapse view
Description Amount
two $2.00
three $3.00
six $6.00
one $1.00
four $4.00
five $5.00
Focussing a cell in the second column I get from [A-Z]
- Code: Select all Expand viewCollapse view
Description Amount
one $1.00
two $2.00
three $3.00
four $4.00
five $5.00
six $6.00
and [Z-A]
- Code: Select all Expand viewCollapse view
Description Amount
six $6.00
five $5.00
four $4.00
three $3.00
two $2.00
one $1.00
In either case the sorted range gets selected if I focussed just a single cell.
menu:Data>Sort... gives the same 4 results, sorting each of the two columns in ascending and descending order.
I append values "seven" and 7 in row #8, click [A-Z], [A-Z] and to my surprise the new row gets included although I did not insert a new row of cells into the existing data range. Everything sorts perfectly.
Alphabetical by column A ("Description"):
- Code: Select all Expand viewCollapse view
Description Amount
five $5.00
four $4.00
one $1.00
seven $7.00
six $6.00
three $3.00
two $2.00
the reverse order is exactly revers, numeric sorting of column B ("Amount") works as well.
Now I define a database range in menu:Data>Define... with "List" as name, $Sample.$A$1:$B$19 as reference and first row as column headers. It includes the whole assumed list including the yet empty cells.
I repeat all the above tests, append "eight" and 8, do it again and everything sorts just perfectly, but now selecting the whole defined data range A1:B19.
I clear some values in both columns and everything sorts as expected.
Completely empty rows are excluded from sorting. They always stick at the bottom of the list whereas empty values with associated neigbour values are sorted (empty is considered larger than any number or text, all text is larger than any number).
The developers did a very good job when they newly implemented the spreadsheet sorting after so many years of valid complaints. One embarrassing error
issue 101690 slipped in, though. But that bug has nothing to do with the constant values in this example.