+ Reply to Thread
Results 1 to 6 of 6

Searchable drop-down lists, no VBA

  1. #1
    Registered User
    Join Date
    01-14-2020
    Location
    Arlington, VA
    MS-Off Ver
    2010
    Posts
    3

    Searchable drop-down lists, no VBA

    Hi all -

    I have a sheet with multiple columns utilizing data validation drop-downs. I need the drop-downs to be searchable due to the large number of options in the drop-down lists (Country column for example). I understand there are limitations with data validation and this can be done with Comboboxes. Unfortunately, I am unable to email Macro-Enabled workbooks so Comboboxes are a no-go. Is there some kind of workaround to solve this?

    Really hoping one of you excel guru's can help!! Thanks everyone in advance.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,895

    Re: Searchable drop-down lists, no VBA

    How many different vaklues do you have? I believe the limit for a DV dropdown is ~32000

    Why are you unable to email the workbook? If it contains confidential then anonymise the names or other data. We don't mind how many M. Mouse, D. Duck or even D. Trumps you have
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    01-14-2020
    Location
    Arlington, VA
    MS-Off Ver
    2010
    Posts
    3

    Re: Searchable drop-down lists, no VBA

    Hi Richard,

    Thank you for your quick reply! I'm such a newbie I honestly didn't even know I could attach the workbook. To answer your first question, I don't have that many values. The most I will have is probably 200/250. Still enough that the users of the workbook don't want to scroll through them and have asked for a solution to make the drop-downs 'searchable'.

    The attached workbook has been 'slimmed' down to simply contain two examples of drop-downs that are used.

    Thanks again!
    Attached Files Attached Files

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,895

    Re: Searchable drop-down lists, no VBA

    A Data Validation drop down is essentially the same as a VBA UserForm Combo box. They both allow you to search for a single value amongst many. i.e. they are both searchable. And by searchable I mean you can select a whole name.

    With DV you can't type in say "Ch" and expect to be presented with a choice of "Chad, Chile, China and Christmas Island"
    There is one way you could do this within an ordinary cell. There's the Autofill optionm whereby if you have a list of allowable values, the blank cell immediately underneath the list will react to characters as you type them in and as soon as it identifies a unique set of characters it will fill the whole cell with the name it has found.
    See example in A277 on then Drop Down sheet.

    A VBA macro and a combo box could identify all countries that match a string of characters and allow you to select one but you seem to have ruled out VBA.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-14-2020
    Location
    Arlington, VA
    MS-Off Ver
    2010
    Posts
    3

    Re: Searchable drop-down lists, no VBA

    Thanks Richard!

    Yes, VBA won't work because I need to be able to email this workbook and our IT department prohibits emailing Macro-Enabled workbooks.

    In your example, is there a way to have the 'searchable' cells be on the 'QRR Form' tab vs. at the bottom of the list of values on the 'Drop Down Tables' tab like they are now? Or is that why it works because it's directly below all of the values? If that's the case, I don't think that's going to work.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    25,895

    Re: Searchable drop-down lists, no VBA

    Unfortunately yes, that's just the way it works.

    The best you can do is hide the rows above the cell entry.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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