Formula help - need to be able to sort alpabetically so logical values don't work

[jessicaconn]

I have created a workbook in Excel 2002 that has 4 worksheets. 2 of
them interact with each other. One of the worksheets is for a client
to enter detailed data into and the other automatically populates from
some of the first worksheet. My problem is with the worksheet that
basically just contains formulas in order to bring the data over from
the other worksheet. I need to be able to do 2 things that I can't
seem to do together. I need the data to be brought over and if the
cell is blank I need it to return an empty cell, not a 0. I used a
logical value formula to do that, but then because of the default
sorting, when I try to sort the list alphabetically it puts all of the
blank cells first (TRUE before FALSE). I need to find a way to
populate the worksheet with data and still be able to sort
alphabetically and not have a bunch 0's in the cells that brought over
"blank" data. Any ideas? Does this post even make sense? It's hard
to describe without being able to show it to you.

Jessica

 

[Guest]

Since the letter "Z" is the last character in Excel's sort sequence, you
can't really have anything sorted after it. (Except empty cells, but if the
cell contains a formula, it isn't empty, no matter what value it returns)

A couple ideas come to mind that may help.

One, is to return a value of "ZZZ" for blank cells. That way, they'd always
be sorted last. If you really don't want the "ZZZ" to appear though, you
could use Conditional Formatting to change the font color to match the
backgroung (ie white on white) for any cell containing "ZZZ".

Another option would be to use a helper column. Insert another column next
to your data. In that column, use a formula like =IF(A1="",2,1). Then, when
you sort, sort by the helper column first, then by your actual data column.
This basically divides your data into two categories, blank and non-blank.
All non-blank data (value of 1) will be sorted first, then all blank data
(value 2) will be sorted last.

HTH,
Elkar