Problem with Advanced Filter Criteria
 

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

Problem with Advanced Filter Criteria
 

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



--
Regards,

Peo Sjoblom





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

Problem with Advanced Filter Criteria
 

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



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


Dates Dates
criteria criteria


Peo

Problem with Advanced Filter Criteria
 

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

"Peo Sjoblom" wrote:

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



--
Regards,

Peo Sjoblom





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

Problem with Advanced Filter Criteria
 

Thanks for the feedback


Peo


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