+ Reply to Thread
Results 1 to 9 of 9

How To Filter Data From Drop Down List Selection In Excel?

  1. #1
    Registered User
    Join Date
    07-13-2012
    Location
    Bristol
    MS-Off Ver
    Office 365
    Posts
    25

    How To Filter Data From Drop Down List Selection In Excel?

    Hi All,


    I have a workbook with two sheets. One contains applications. On the other I would like two drop down filter boxes ( This can be on the first sheet if that is easier) "Project Lead" and "Period"

    For example I would like to be able to pull the data from the first sheet for a "Anne" applications in 2012/13 to appear on the second sheet called search.

    I've got as far as creating a drop down of the names using a list and data validation - now I'm stuck and can't get any further. Any and all help would be appreciated.

    For reference, the data tab normally has about 2000 rows.

    I think I've attache an example, let me know if this didn't work.

    Many thanks for looking at this in advance.
    Attached Files Attached Files
    Last edited by debora; 08-14-2020 at 10:41 AM.

  2. #2
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,265

    Re: How To Filter Data From Drop Down List Selection In Excel?

    The attached is using your two dropdowns - they are re-positioned under relative headers.
    select as normal and press green button - an advanced search is coded in a VBA module.
    Any queries post back on this site.
    torachan.
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Here is a formula based answer
    In Data Sheet, Column L, I added "Key" column
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    This column can be hidden if you like
    Then in Search sheet A9 copied across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    07-13-2012
    Location
    Bristol
    MS-Off Ver
    Office 365
    Posts
    25

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Thank you so much for this - its amazing.

  5. #5
    Registered User
    Join Date
    07-13-2012
    Location
    Bristol
    MS-Off Ver
    Office 365
    Posts
    25

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Thank you so much

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,322

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Search


    A9=FILTER(Data!A8:K1000,(Data!C8:C1000=!B4)*(Data!J8:J1000=Search!B5),"")

  7. #7
    Registered User
    Join Date
    07-13-2012
    Location
    Bristol
    MS-Off Ver
    Office 365
    Posts
    25

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Then in Search sheet A9 copied across and down
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    [/QUOTE]

    Thank you so much for this, it does exactly what I need - if I wanted to duplicate this, but search on the name only, and removed the date filter option, how would I edit the formula. I'm guessing its not as simple as removing the reference to $B$5

  8. #8
    Registered User
    Join Date
    07-13-2012
    Location
    Bristol
    MS-Off Ver
    Office 365
    Posts
    25

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Quote Originally Posted by torachan View Post
    The attached is using your two dropdowns - they are re-positioned under relative headers.
    select as normal and press green button - an advanced search is coded in a VBA module.
    Any queries post back on this site.
    torachan.
    Thanks for this, but how could i edit this to run on different sheets, for example if I wanted a second version of this which only searches on the name?

  9. #9
    Forum Expert
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    3,265

    Re: How To Filter Data From Drop Down List Selection In Excel?

    Copy present file > remove data validation (cell J2) > retain data validation (cell C2) > open macro and select "FIND" > select edit.
    Find the advanced filter row and change the CriteriaRange as attached below.

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to Amend the Filter Selection on a Pivot Table Based on a Drop Down List
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 03-22-2017, 10:00 AM
  2. [SOLVED] Drop Down List Selection funs Filter macro
    By rz6657 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-20-2016, 07:30 PM
  3. Control filter (drop down list with multiply selection) from a different sheet
    By gowthamj in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-23-2014, 01:22 PM
  4. Replies: 5
    Last Post: 04-11-2014, 03:13 PM
  5. Excel VBA multiple selection drop down data validation list
    By garoe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2014, 05:20 PM
  6. Changing Pivot Report Filter based on Drop Down List Selection
    By rajuasq in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-03-2012, 11:40 AM
  7. Excel - how do I use drop down list to filter data
    By Donna in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-10-2006, 03:29 AM

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