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.

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.

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

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

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

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

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.

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