Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove blank cells - no sorting | Excel Worksheet Functions | |||
Sorting Blank Cells | Excel Discussion (Misc queries) | |||
Sorting with blank cells | Excel Discussion (Misc queries) | |||
Sorting and Blank Cells | Excel Worksheet Functions | |||
Sorting and Eliminating Blank Cells in Formula range | Excel Worksheet Functions |