Problem with Advanced Filter Criteria

G

Guest

Hi

I'm struggling with this...

I have data in Col U which is Date + Time e.g. 08/08/07 7:20am

I want to copy the unique values in the range to another location subject to
the criteria >Menu!I5+Time(05,59,59) and <((MenuI5+1)+Time(06,00,00))

I've copied the heading from Col U to CV1 and under that have put the
criteria above in CV2 and CV3

There's a problem with those formulas as criteria or the syntax I'm using.
If I just type the formulas with an = instead of < or > they work. If I copy
and paste special as values the formulas so I get a general format number I
can make the criteria work but that doesn't help as I need this to work
formulaically with no user input.

I would appreciate some help and advice on how to make this work

Many thanks
 
P

Peo Sjoblom

Maybe it would be easier to explain what you want with your criteria, for
instance what's in
Menu!I5? A date? If you are referring to another cell and a formula you
would need to use

=">"&Menu!I5+TIME(5,59,59)

and

="<"&Menu!I5+1+TIME(06,0,0)


if you want to extract the values between (AND criteria) those times you
should use 2 headers (the same header in this case) so in CV1 and CW1 use
the header and the values in CV2 and CW2




Dates
Dates
=">"&Menu!I5+TIME(5,59,59) ="<"&Menu!I5+1+TIME(6,0,0)

of course in the criteria cells you will get a number with decimals
 
P

Peo Sjoblom

Dates Dates
=">"&Menu!I5+TIME(5,59,59) ="<"&Menu!I5+1+TIME(6,0,0)

of course in the criteria cells you will get a number with decimals
Click to expand...


Of course the bloody OE can't handle text very well, anyway the headers
should be


Dates Dates
criteria criteria


Peo
 
G

Guest

That's pefect Peo. Thanks a lot. I must have tried everything but that and
it's so obvious once you see it.

Thanks again
 
Top