Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filter criteria | Excel Discussion (Misc queries) | |||
Advanced Filter (Criteria + Blanks) | Excel Discussion (Misc queries) | |||
Advanced Filter criteria (formula) | Excel Worksheet Functions | |||
Advanced filter and Criteria Range | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions |