+ Reply to Thread
Results 1 to 13 of 13

Search (Filter) Dropdown list

  1. #1
    Registered User
    Join Date
    01-17-2019
    Location
    US
    MS-Off Ver
    2016
    Posts
    9

    Search (Filter) Dropdown list

    Products (Sheet) has all products in Column B6:B200

    Purchases (Sheet) C5:C500 are all Drop down boxes that link to data in Products B6:B200, I can manually scroll and select an item but would like to be able to type the first few characters and have only matches show up in drop down box.
    Attached Images Attached Images

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,766

    Re: Search (Filter) Dropdown list

    It's not possible to do it in Data Validation

  3. #3
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    785

    Re: Search (Filter) Dropdown list

    Does something like this work for you?
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: Search (Filter) Dropdown list

    Sorry for off-topic interjection:

    @Croweater

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    Last edited by AliGW; 07-30-2020 at 05:06 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    Re: Search (Filter) Dropdown list

    @Croweater, it does not work

  6. #6
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    785

    Re: Search (Filter) Dropdown list

    It works for me...type a (partial) name in B2, then use the dropdown (in B2) to reduce the list in D2:D20

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: Search (Filter) Dropdown list

    It works fine, but without an explanation of how you did it, it's not much use to anyone.

  8. #8
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    785

    Re: Search (Filter) Dropdown list

    If you unhide the helper columns I've hidden (just to be tidy), it will show how it's done.

    It searches the list of names for the partial match entered to create a variably sized helper list

    Then you count the number of names in the new list, name the list (using the count of the number of items as the number of rows in the list) and use that in the data validation.
    Last edited by Croweater; 07-30-2020 at 05:39 AM.

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,124

    Re: Search (Filter) Dropdown list

    Can you please share the formula and locations thereof in a post? I know how to back engineer solutions, but many members would not know where to start. As I have mentioned to you before, some will not be able to download your example file, so a step-by-step explanation would really help to benefit the whole community. Thanks for your co-operation.
    Last edited by AliGW; 07-30-2020 at 05:43 AM.

  10. #10
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    785

    Re: Search (Filter) Dropdown list

    In C2:C20

    =IF(ISNUMBER(SEARCH($B$2,D2)),MAX($C$1:C1)+1,0)

    This puts a (progressive) number against each item where there is a partial match

    In F2:F20

    =IF(ISERROR(VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0)),"",VLOOKUP(ROWS($F$2:F2),$C$2:$D$20,2,0))

    This uses a row number to do a vlookup in the range C2:D20 to create a (variably sized) list

    Then you create a named list with this as the source...

    =OFFSET($F$2,,,COUNTIF($F$2:$F$19,"?*"))

    The COUNTIF is the number of non blank items in the helper list. The OFFSET function allows you to use the variably sized list in the named list in the data validation.

  11. #11
    Valued Forum Contributor Haluk's Avatar
    Join Date
    02-14-2019
    Location
    Türkiye
    MS-Off Ver
    2010 - 64 Bit on Win7
    Posts
    948

    Re: Search (Filter) Dropdown list

    The same topic was discussed yesterday and the link is given below:

    https://www.excelforum.com/excel-gen...ml#post5372295

  12. #12
    Valued Forum Contributor
    Join Date
    10-23-2012
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2003, Office 365
    Posts
    785

    Re: Search (Filter) Dropdown list

    Quote Originally Posted by torachan View Post
    @Croweater, it does not work
    You need to turn the error alert off in the Data Validation.

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16/365
    Posts
    52,004

    Re: Search (Filter) Dropdown list

    Croweater, I believe I have asked you in the past to not just post a file, but to include an explanation on your post as well. It's not too much to ask, so please do so
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ 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. Search all dialog box missing from filter dropdown menu
    By ladyrae42 in forum Excel General
    Replies: 0
    Last Post: 11-19-2018, 12:40 PM
  2. Dropdown list that can apply a filter.
    By 03staylo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2016, 06:50 AM
  3. [SOLVED] Create an Excel Drop Down list with Search Suggestions ( Serachable Dropdown list )
    By PMH2384 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-11-2016, 12:03 AM
  4. Dropdown list search box using VBA
    By hanif in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-10-2014, 05:59 AM
  5. How To Filter List Using Dropdown Function
    By marcosolvs in forum Excel General
    Replies: 1
    Last Post: 07-11-2014, 06:43 PM
  6. How can I use a dropdown list to filter my data?
    By migdad in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-09-2012, 03:35 PM
  7. How to search/filter data in Excel dropdown having large number of values
    By sampr in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2007, 10:20 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