Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filter by combobox (2000)

    Hi,
    Is it possible to filter a workbook that has dates in a column based on what is selected in a combobox.
    In the workbook that I need to do this on I, have a column (E) that has dates in the following format dd/mm/yyyy. I would like to be able to filter the record based on what the user selects in two comboboxes (year and month) and just display those records that relate to that period.
    I'd then be calculating the unhidden values for the appropriate month.

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

    Re: Filter by combobox (2000)

    It would be easiest to use either AutoFilter or Advanced Filter for this. You can add calculated columns for the month and year, if you like.

    If you only want to perform calculations on filtered values, you can use the database functions such as DCOUNT, DSUM etc. - look them up in the online help.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by combobox (2000)

    Presumably this is the only way. The document that I need to do this on is being sent to someone who has little knowledge of Excel, hence the combobox approach. I did wonder if it were possible to effectively reproduce the advanced filter in the format of a combobox.

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

    Re: Filter by combobox (2000)

    You can use combo boxes too, with a bit of code. See the attached very crude example; it uses controls from the Forms toolbar; it could also be done (but slightly differently) using controls from the Control Toolbox.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Filter by combobox (2000)

    In addition to the Hans demo of the adv filter, you could also do it similarly with the autofilter to filter on a custom date range.

    The advantage of the autfilter is that it does not require the "intermediate criteria range" and can work as a normal autofilter if desired.

    I generally would use (as already suggested) intermediate columns of Year, Month/Year, and also if desired Month and then use the autofilter without any coding. It is straightforward, generally intuitive to users and requires no coding.

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by combobox (2000)

    Sorry to be a pain, but how do you actually get the data to filter dependant upon what is selected? I've had a look at the coding and I can see the it specifies criteria, I'm just not too sure how it works.

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

    Re: Filter by combobox (2000)

    You messed up the definition of the ranges named Database and Criteria. And you added a superfluous "" to the code. See corrected version.

    The idea is that the data form a range named "Database" and the criteria are specified in a range named "Criteria". In the corrected version, this is O1:P2. We need a separate range because we need to add 2000 to the cell linked to the Year combo box. The linked cell contains the index of the selected item, not the selected item itself.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by combobox (2000)

    Thanks Hans. Works a treat.
    I didn't quite understand how the database and criteria were set.
    Having looked at what you've done, I presume that its by going Insert>Name>Define and specifying cell references here. And from this the coding is able to relate to the Database and the criteria.
    I really should use excel more often........................... <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Filter by combobox (2000)

    Yes, you define named ranges in the Insert | Name | Define dialog, or by selecting a range and typing the name in the cell address box in the formula bar.

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filter by combobox (2000)

    Thanks again Hans. Your help has been invaluable.
    I quite like to know how the processes work, so thank you for clarifying it.

Posting Permissions

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