GreenTree
12-13-2009, 09:57 PM
I have a spreadsheet that I distribute to several people (of widely varying skill levels) that is created with VBA but contains no VBA in it, since I try to keep it readable with as many programs as possible (Open Office, Think Free, etc). One of the columns in the sheet contains various numbers as text. (Why, you ask? Because they're pulled from any of several other columns of data, and to keep the format the same as the source {one column is 58.2%, another is 8, another is 10:22 AM, etc}, I've had to make the data show up in text format.) This column is also one which people will frequently want to sort on.
When I open the sheet and use Data | Sort to do so, I get the expected prompt noting that there are numbers as text, and do I want to sort everything that looks like a number as a number (yields the expected 8, 9, 10, 11 sequence) or to sort everything as text (yields 10, 11, 8, 9). I select the former, and life is good. HOWEVER...
...if on the other hand the first sort I perform (this is using Excel 2003, haven't tried other versions yet) is done using the AutoFilter drop-down arrow and the "Sort Ascending" option, I'm not prompted, and the default assumption is the one I don't want, i.e. to sort everything as text. This causes some very unexpected sorting results, to say the least!
The obvious work-around is to tell everyone to please sort with Data | Sort (at least initially) and not with the drop-down arrows. I'm curious, though, if there may be something I can do in the construction of the worksheet to force the sorting to "sort everything that looks like a number, as a number."
Anyone?
Thanks,
G.T.
When I open the sheet and use Data | Sort to do so, I get the expected prompt noting that there are numbers as text, and do I want to sort everything that looks like a number as a number (yields the expected 8, 9, 10, 11 sequence) or to sort everything as text (yields 10, 11, 8, 9). I select the former, and life is good. HOWEVER...
...if on the other hand the first sort I perform (this is using Excel 2003, haven't tried other versions yet) is done using the AutoFilter drop-down arrow and the "Sort Ascending" option, I'm not prompted, and the default assumption is the one I don't want, i.e. to sort everything as text. This causes some very unexpected sorting results, to say the least!
The obvious work-around is to tell everyone to please sort with Data | Sort (at least initially) and not with the drop-down arrows. I'm curious, though, if there may be something I can do in the construction of the worksheet to force the sorting to "sort everything that looks like a number, as a number."
Anyone?
Thanks,
G.T.