Blanks and Advanced Filters
 

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.

Blanks and Advanced Filters
 

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



"Janie" wrote:

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.

Blanks and Advanced Filters
 

Janie wrote:
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

Blanks and Advanced Filters
 

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.

Janie wrote:
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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Blanks and Advanced Filters
 

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



"Debra Dalgleish" wrote in message
...
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.

Janie wrote:
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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Blanks and Advanced Filters
 

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

"Peo Sjoblom" wrote:

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



"Debra Dalgleish" wrote in message
...
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.

Janie wrote:
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.


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Blanks and Advanced Filters
 

thanks to Debra for the right answer:
"Debra Dalgleish" wrote in message
...
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.


"Janie" wrote:

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.

Blanks and Advanced Filters
 

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

Janie wrote:
thanks to Debra for the right answer:
"Debra Dalgleish" wrote in message
...

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.



"Janie" wrote:


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.



--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html