Blanks and Advanced Filters

[Guest]

Using <>* works fine in an Advanced Filter when the field searched is TEXT,
but it does not work on numbers or dates. What's a good way of writing an
Advanced Filter that looks for blanks in a column with dates or numbers?

Examples:
Data range is like this:

TEXT NUMBER DATE
A 1 8/21/06
B 8/20/06
3 8/19/06
D 4

If Advanced Filter is TEXT <>* then you get that third row -- the one with
the blank. BUT, if the Advanced Filter is NUMBER <>* you get all records.
Same is true for the DATE column.

 

[Guest]

Is it not possible for you to use Auto Filter and select blanks from the
dropdown menu??

 

[Franz Verga]

Using <>* works fine in an Advanced Filter when the field searched is
TEXT, but it does not work on numbers or dates. What's a good way of
writing an Advanced Filter that looks for blanks in a column with
dates or numbers?

Examples:
Data range is like this:

TEXT NUMBER DATE
A 1 8/21/06
B 8/20/06
3 8/19/06
D 4

If Advanced Filter is TEXT <>* then you get that third row -- the one
with the blank. BUT, if the Advanced Filter is NUMBER <>* you get
all records. Same is true for the DATE column.

For the DATE column you can use >0. For the number column you can use also

0 and also <0 (if there is the possibility to have numbers <0...)

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Debra Dalgleish]

In the criteria area, leave the heading cell blank.
In the cell below, enter a formula that refers to the first row of data
in the table. For example, if the numbers are in column F:

=ISBLANK(F2)

When you apply the Advanced Filter, select the blank heading cell and
the cell with the formula, as the criteria range.

 

[Peo Sjoblom]

Another way would be to use a header in F1 and put an equal sign in F2
or if the blanks could be from a formula one could use blank header and

=F2=""


Peo

 

[Guest]

Debra had the right answer! Sorry, Peo, yours did not work

 

[Guest]

thanks to Debra for the right answer:

In the criteria area, leave the heading cell blank.
In the cell below, enter a formula that refers to the first row of data in
the table. For example, if the numbers are in column F:

=ISBLANK(F2)

When you apply the Advanced Filter, select the blank heading cell and the
cell with the formula, as the criteria range.

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it worked.