Sorting blank cells with a formula in them
 

Does a formula in a cell constitute a blank cell, or not? The formula is an
IF formula connected to a different worksheet resulting either in date or "",
so most of my cells are empty. When I try to sort this new sheet, all the
blank rows / cells come up first, which supposedly shouldn't happen, but I'm
assuming that's because Excel is reading the "" result as data or an entry.
Is there a way around this?

Sorting blank cells with a formula in them
 

I haven't tried this - but try making the IF function equal to a date or
equal to a truly blank cell...like Z500...or whatever.

"EMW103" wrote:

Does a formula in a cell constitute a blank cell, or not? The formula is an
IF formula connected to a different worksheet resulting either in date or "",
so most of my cells are empty. When I try to sort this new sheet, all the
blank rows / cells come up first, which supposedly shouldn't happen, but I'm
assuming that's because Excel is reading the "" result as data or an entry.
Is there a way around this?

Sorting blank cells with a formula in them
 

Sorry, Steve, mispell: data, not date (though same thing in terms of how the
sheet is functioning). I didn't understand your respose: "equal to a truly
blank cell"? If "" in a formula doesn't equal a truly blank cell, what does?
Otherwise I would get "FALSE" or "VALUE" or something, which Excel would
also read as a cell entry, no?

"Steve" wrote:

I haven't tried this - but try making the IF function equal to a date or
equal to a truly blank cell...like Z500...or whatever.

"EMW103" wrote:

Does a formula in a cell constitute a blank cell, or not? The formula is an
IF formula connected to a different worksheet resulting either in date or "",
so most of my cells are empty. When I try to sort this new sheet, all the
blank rows / cells come up first, which supposedly shouldn't happen, but I'm
assuming that's because Excel is reading the "" result as data or an entry.
Is there a way around this?

Sorting blank cells with a formula in them
 

If "" in a formula doesn't equal a truly blank cell, what does?

A truly blank cell is what you get by selecting the cell and hitting the
Delete key. It is neither text nor numeric nor logical.

By contrast, "" is a text string of zero length, one character shorter than
"b".

Just as "b" sorts before "ba", so does "" sort before "a"; just remove the
initial "b" from each to see why.

To see the difference a bit better, put this formula in B1:
=IF(ISTEXT(A1),"T","")&
IF(ISNUMBER(A1),"N","")&
IF(ISLOGICAL(A1),"L","")&
IF(ISBLANK(A1),"B","")&
IF(ISNONTEXT(A1)," ~T","")
and put different kinds of things in A1.

I haven't found any way in Excel for a formula to result in a truly blank
cell.


When I try to sort this new sheet, all the blank rows / cells come
up first ... Is there a way around this?

Maybe by using "ZZZZZZ" instead of "", and (if necessary) finding a way to
hide the Z's.