+ Reply to Thread
Results 1 to 6 of 6

Need Advanced Filter with NOT equal string and OR criteria

  1. #1
    MK
    Guest

    Need Advanced Filter with NOT equal string and OR criteria

    I have a project timeline laid out in Excel. I'm trying to write an Advanced
    Filter that will show me everything that has a "marker" in a specific week's
    column, OR is a milestone, AND IS NOT completed.

    The criteria below works for everything but the IS NOT COMPLETED part of the
    query. What do I have to put under "Status", and in what rows, to filter OUT
    those items that are completed?

    Milestone? 5-Dec 12-Dec 19-Dec Status
    Yes
    *
    *
    *



  2. #2
    Peo Sjoblom
    Guest

    Re: Need Advanced Filter with NOT equal string and OR criteria

    How do you tag those that are completed in the status column?
    Just "Completed"? if so for not completed use

    ="<>Completed"


    --

    Regards,

    Peo Sjoblom



    "MK" <MK@discussions.microsoft.com> wrote in message
    news:0BBE998C-92DE-44C9-BDE6-74920CCACB46@microsoft.com...
    > I have a project timeline laid out in Excel. I'm trying to write an

    Advanced
    > Filter that will show me everything that has a "marker" in a specific

    week's
    > column, OR is a milestone, AND IS NOT completed.
    >
    > The criteria below works for everything but the IS NOT COMPLETED part of

    the
    > query. What do I have to put under "Status", and in what rows, to filter

    OUT
    > those items that are completed?
    >
    > Milestone? 5-Dec 12-Dec 19-Dec Status
    > Yes
    > *
    > *
    > *
    >
    >




  3. #3
    MK
    Guest

    Re: Need Advanced Filter with NOT equal string and OR criteria

    And I have to put that on every line of the criteria, correct? In other
    words, (Milestone?=YES AND Status<>Completed) OR (12/5/05=anything AND
    Status<>Completed) OR ...

    Well, I have tried that, and it returns NOTHING. I will try again from work
    tomorrow, and post the results, just to be sure.
    Thanks, for now!

    "Peo Sjoblom" wrote:

    > How do you tag those that are completed in the status column?
    > Just "Completed"? if so for not completed use
    >
    > ="<>Completed"
    >
    >
    > --
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    >
    >
    > "MK" <MK@discussions.microsoft.com> wrote in message
    > news:0BBE998C-92DE-44C9-BDE6-74920CCACB46@microsoft.com...
    > > I have a project timeline laid out in Excel. I'm trying to write an

    > Advanced
    > > Filter that will show me everything that has a "marker" in a specific

    > week's
    > > column, OR is a milestone, AND IS NOT completed.
    > >
    > > The criteria below works for everything but the IS NOT COMPLETED part of

    > the
    > > query. What do I have to put under "Status", and in what rows, to filter

    > OUT
    > > those items that are completed?
    > >
    > > Milestone? 5-Dec 12-Dec 19-Dec Status
    > > Yes
    > > *
    > > *
    > > *
    > >
    > >

    >
    >
    >


  4. #4
    Peo Sjoblom
    Guest

    Re: Need Advanced Filter with NOT equal string and OR criteria

    Normally you put it in one place

    http://www.contextures.com/xladvfilter01.html


    You need to post back with info of your layout, headers, number of columns
    and contents and what you want to receive when the filter has been applied

    --
    Regards,

    Peo Sjoblom

    (No private emails please)


    "MK" <MK@discussions.microsoft.com> wrote in message
    news:6261FB79-FAA0-4323-80A0-202A816AF2CD@microsoft.com...
    > And I have to put that on every line of the criteria, correct? In other
    > words, (Milestone?=YES AND Status<>Completed) OR (12/5/05=anything AND
    > Status<>Completed) OR ...
    >
    > Well, I have tried that, and it returns NOTHING. I will try again from
    > work
    > tomorrow, and post the results, just to be sure.
    > Thanks, for now!
    >
    > "Peo Sjoblom" wrote:
    >
    >> How do you tag those that are completed in the status column?
    >> Just "Completed"? if so for not completed use
    >>
    >> ="<>Completed"
    >>
    >>
    >> --
    >>
    >> Regards,
    >>
    >> Peo Sjoblom
    >>
    >>
    >>
    >> "MK" <MK@discussions.microsoft.com> wrote in message
    >> news:0BBE998C-92DE-44C9-BDE6-74920CCACB46@microsoft.com...
    >> > I have a project timeline laid out in Excel. I'm trying to write an

    >> Advanced
    >> > Filter that will show me everything that has a "marker" in a specific

    >> week's
    >> > column, OR is a milestone, AND IS NOT completed.
    >> >
    >> > The criteria below works for everything but the IS NOT COMPLETED part
    >> > of

    >> the
    >> > query. What do I have to put under "Status", and in what rows, to
    >> > filter

    >> OUT
    >> > those items that are completed?
    >> >
    >> > Milestone? 5-Dec 12-Dec 19-Dec Status
    >> > Yes
    >> > *
    >> > *
    >> > *
    >> >
    >> >

    >>
    >>
    >>



  5. #5
    MK
    Guest

    Re: Need Advanced Filter with NOT equal string and OR criteria

    Okay, I'll try to lay this out. The problem now is, using the Completed
    criteria you gave me (and that _is_ how I indicated if they're completed), I
    don't get lines returned that have NOTHING in the STATUS column.
    Here's my timeline (I left off the row/column letters/numbers):

    Event Milestone? Start End Status 11/28/05
    12/5/05 12/12/05
    Item 1 12/5 12/9 Completed
    XXXXX
    Item 2 12/5 12/16 In Process
    XXXXX XXXXX
    Item 3 12/5 1/13
    XXXXX XXXXX
    Item 4 Yes 3/31 4/5

    (Item 4 would have x's way out in the columns for those future dates.)

    My criteria could be JUST

    Status
    ="<>Completed"
    Status

    And all that would be returned would be the IN PROCESS item (#2).

    What I REALLY want is criteria like
    Milestone? 12/5 Status
    Yes ="<>Completed"
    * ="<>Completed"

    to indicate that I want to see anything that's (NOT completed) AND (that is
    supposed to occur any time during the week of 12/5 OR that is a milestone
    (occurring any time).)

    When I run that last query, though, I still DO NOT SEE BLANK STATUS items.
    I really don't want to have to change all my blanks to NOT STARTED, but that
    _does_ work. Why isn't it returning NULL values?

    Thanks - hope this is readable!

  6. #6
    Peo Sjoblom
    Guest

    Re: Need Advanced Filter with NOT equal string and OR criteria

    I really can't see how your layout is but ="<>Completed"
    will return everything that does not have "Completed" both "In Process" and
    blanks in the Status column so presumably the problem lies in your criteria
    when using the dates and milestones

    1. I notice that you are using 12/5, do you realise that if you just type in
    m/d it will always default to current year?

    If you want to start with 12/05/05 as a starting date and milestone yes and
    not completed you need something like this for the criteria

    Milestone Start Status
    Yes ="=12/05/05" <>Completed


    --

    Regards,

    Peo Sjoblom




    "MK" <MK@discussions.microsoft.com> wrote in message
    news:6794382D-487D-41FB-8D83-A81C1DF88EE8@microsoft.com...
    > Okay, I'll try to lay this out. The problem now is, using the Completed
    > criteria you gave me (and that _is_ how I indicated if they're completed),

    I
    > don't get lines returned that have NOTHING in the STATUS column.
    > Here's my timeline (I left off the row/column letters/numbers):
    >
    > Event Milestone? Start End Status 11/28/05
    > 12/5/05 12/12/05
    > Item 1 12/5 12/9 Completed
    > XXXXX
    > Item 2 12/5 12/16 In Process
    > XXXXX XXXXX
    > Item 3 12/5 1/13
    > XXXXX XXXXX
    > Item 4 Yes 3/31 4/5
    >
    > (Item 4 would have x's way out in the columns for those future dates.)
    >
    > My criteria could be JUST
    >
    > Status
    > ="<>Completed"
    > Status
    >
    > And all that would be returned would be the IN PROCESS item (#2).
    >
    > What I REALLY want is criteria like
    > Milestone? 12/5 Status
    > Yes ="<>Completed"
    > * ="<>Completed"
    >
    > to indicate that I want to see anything that's (NOT completed) AND (that

    is
    > supposed to occur any time during the week of 12/5 OR that is a milestone
    > (occurring any time).)
    >
    > When I run that last query, though, I still DO NOT SEE BLANK STATUS items.
    > I really don't want to have to change all my blanks to NOT STARTED, but

    that
    > _does_ work. Why isn't it returning NULL values?
    >
    > Thanks - hope this is readable!




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1