Advance Filtering using Formulas

[Mike Lynch]

Can someone help me understand the error in my logic for
filtering a list of times with a formula (IF statement
used to select a RANGE). It apears only to filter values
that exactly match those of cell "A6" (if it is in the
range) and not those represented by the function. (Note
same problem exist when using integers in place of times,
so it should not be related to the use of the TIME
function)

I would prefer not to use the "autofilter" because I'm
also filtering a LONG list on non-continuous numbers
(which are a pain to select from auto filter check boxes
and change frequently). Example,

Criteria range (A1:A2)
Time
=IF(A6>=TIME(0,3,0),IF(A6<=TIME(0,4,0),A6,"OVER"),"UNDER")

List range (A5:A10):
Time
00:02:59
00:03:00
00:03:30
00:04:00
00:04:01

Copy to: A12

Thanks,
Mike

 

[Debra Dalgleish]

Change the formula to:

=AND(A6>=TIME(0,3,0),A6<=TIME(0,4,0))

and remove the heading from cell A1. When you use a formula in the
criteria range, that column should not have a heading that matches the
list heading.

 

[Mike Lynch]

Good news, removed column header and replaced criteria,
filter is now passing data through.... bad news EVERYTHING
is passing through. When I run this I get all the original
data as if it was not filtered at all. H'mmmm

 

[Debra Dalgleish]

Did you select cells A1:A2 as the criteria range? Even though the
heading cell is blank, it must be included.