Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter for a certain value OR blank cell (2003/SP2

    I'm trying to set up an advanced filter that returns records in which the date column contains EITHER a date later than November 30, 2007, OR nothing at all (blank cell).

    Here's what I have in the relevant cell in the criteria range:

    OR(>11/30/2007,ISBLANK)

    It's returning the >11/30/2007 cells, but not the blank ones. Is my syntax off?

    Thanks in advance,
    Erik

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for a certain value OR blank cell (2003/SP2

    In the criteria range, leave the field name (column header) blank.
    In the cell below it, enter this formula:

    =OR(D2>DATE(2007,11,30),ISBLANK(D2))

    where D2 is the first cell below the field name in the date column in the data range.
    When you specify the criteria range for Advanced Filter, include the blank field name above the formula!

  3. #3
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for a certain value OR blank cell (2003

    My syntax was off, all right. Thanks, HansV.

    Related question: as I experiment with my advanced filters, is there a way to update a filter quickly without recreating it from scratch (selecting database, criteria, and extraction ranges) every time?

    Erik

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for a certain value OR blank cell (2003

    Excel "remembers" the database, criteria and extraction ranges, so you can update as follows:
    - Click anywhere in the database range.
    - Select Data | Filter | Advanced Filter...
    - Click 'Copy to another location'
    - Click OK.

    If you need to do this often, you could create a macro to do this for you, and assign it to a toolbar button and/or keyboard shortcut.

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for a certain value OR blank cell (2003

    Actually, that doesn't work for me. I still have to re-find the criteria and extract ranges, and then I get an error message saying "You can only copy filtered data to the active sheet." I guess I could try a macro, but why would the error message appear?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for a certain value OR blank cell (2003

    If you want to use advanced filter to extract to another sheet, you *must* select Data | Filter | Advanced Filter from the destination sheet, but that's a PITA, because it makes Excel "forget" the database range. A macro would definitely be better for that.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for a certain value OR blank cell (2003

    I have attached a simple example workbook. The Data sheet contains the data table, the criteria range and a command button from the Forms toolbar. The Extract sheet contains the 'extract to' range.
    The command button calls a macro that executes Advanced Filter:
    <code>
    Sub RunAdvancedFilter()
    Worksheets("Data").Range("Database").AdvancedFilte r _
    Action:=xlFilterCopy, _
    CriteriaRange:=Worksheets("Data").Range("Criteria" ), _
    CopyToRange:=Worksheets("Extract").Range("Extract" )
    End Sub
    </code>
    Database, Criteria and Extract are named ranges, defined in Insert | Name | Define. Database is a dynamic range, it will automatically adjust itself if rows with data are added or deleted.
    Attached Files Attached Files

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Seattle, Washington, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter for a certain value OR blank cell (2003

    Sorry for the delayed reply -- your last 2 messages did not appear in my Inbox. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

    Many thanks for the macro. Great help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filter for a certain value OR blank cell (2003


Posting Permissions

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