sorting: cleared/null v/s ""

[cate]

When you sort in Ex2010, a cell with a value of "cleared/null" (set so
via mouse menu "Clear Contents" or vba range.ClearContents.) gets you
a result with nulls last in both sorts - ascending and descending.

However, if you set the cell's "empty value" as an empty string, = "",
they are considered "values" and sort. I see posts that claim "" ==
null. It doesn't seem so.

I have a formula that, based on existence of a date, sets a value in a
cell. I want either a date or CLEAR/NULL but can't find something
that actually clears or nulls the value. I'm guessing I'll have to
create a UDF (if I can actually set a null with a return "value"). Is
that correct?

Have

=IF(
AND(
NOT(ISNA(VLOOKUP(D10,nt_po_number,2,TRUE)))
),
VLOOKUP(D10,nt_po_number,2,TRUE),
""
)

Want

=IF(
AND(
NOT(ISNA(VLOOKUP(D10,nt_po_number,2,TRUE)))
),
VLOOKUP(D10,nt_po_number,2,TRUE),
NULL
)

 

[cate]

It looks like I'm dead - no go. The new question is then: is there a
sort configuration that say empties last? (nulls last)
Thank you for any suggestions.

 

[KenCowen]

Cate
You could possibly add an additional column, with an IF formula that equalsthe intended sort column, unless the sort column is blank or null or otherwise problematic, in which case the formula returns ZZZ or something that will sort as you wish; then sort by the new column.
Ken

 

[cate]

Cate
You could possibly add an additional column, with an IF formula that equals the intended sort column, unless the sort column is blank or null or otherwise problematic, in which case the formula returns ZZZ or something thatwill sort as you wish; then sort by the new column.
Ken

Good idea. Thank you Ken.