Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / December 2005

Tip: Looking for answers? Try searching our database.

Advanced Filter criteria (formula)

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Gareth - 19 Dec 2005 19:16 GMT
I was using the following formula when filtering a sheet of data:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<>0,K2=0,I2>=ayearago))

It filters all rows with 1 in column B and any other rows with 2 or 3 in
column B that meet the criteria L2<>0,K2=0,I2>=ayearago (where ayearago is a
named cell containing a date).

I now find myself needing to change this formula to filter for all rows with
1 in column B and any other rows with 2 or 3 in column B that meet the
criteria L2<>0,K2=0,I2>=ayearago and L2<>0,K2>=thirtydaysago,I2>=ayearago.

I have tried but with no success so far.

Thanks in advance.

Gareth
Roger Govier - 19 Dec 2005 23:57 GMT
Hi Gareth
If I understand the problem correctly, then
=AND(OR(B2>0,B2<=3),OR(K2=0,K2>=thirtydaysago),L2<>0,I2>=ayearago)

Regards

Roger Govier

> I was using the following formula when filtering a sheet of data:
>
[quoted text clipped - 13 lines]
>
> Gareth
Art Farrell - 20 Dec 2005 19:12 GMT
Hi Gareth,

Staying with your original setup of the formula:

=OR(B2=1,AND(OR(B2=2,B2=3),L2<>0,OR(K2=0,K2>="thirtydaysago"),I2>="ayearago"
))

Roger's formula rearranges it, but you need the B2=1, as B2>0 would include
values of 4 on unless I'm mistaken.

=AND(OR(B2=1,B2<=3),OR(K2=0,K2>="thirtydaysago"),L2<>0,I2>="ayearago")

CHORDially,
Art Farrell

> I was using the following formula when filtering a sheet of data:
>
[quoted text clipped - 13 lines]
>
> Gareth
Roger Govier - 20 Dec 2005 21:12 GMT
Hi Art

Nice catch.
You are quite right, my formula would have picked up any value in B2,
outside of the desired range.

Regards

Roger Govier

> Hi Gareth,
>
[quoted text clipped - 34 lines]
>>
>>Gareth
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.