How to Search Data Validation Drop-down Lists in Excel

Bottom line: Learn a fast and easy way to search any data validation list or in-cell drop-down list with a free tool.

Skill level: Beginner

how-to-search-data-validation-lists-in-excel-cover-640x360

Data validation lists are a great way to control the values that are input in a cell.  These drop-down lists also allow us to choose options that can drive financial models, reports, or dashboards.

However, there is no built-in way to search the validation list in Excel.  It can be difficult to scroll through these lists when the drop-down contains a lot of items.  There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file.

no-built-in-way-to-search-data-validation-drop-down-lists-in-excel

So I developed a very simple add-in that helps solve this problem…

Video Overview of List Search

Click here to download the List Search Add-in

Click the links below to jump down to the feature update videos.

Search Validation Lists with List Search

The List Search Add-in makes it fast and easy to search any validation list.  It also works with lists of data that do not contain data validation cells.

List Search Add-in Drop-down List Contains Data Validation List of Items

The List Search form contains a drop-down box that loads the selected cell's validation list.  The drop-down box also functions as a search box.  You can type a search in the box and the results will be narrowed down as you type.  This is a Google-like search and the results will include any item that contains the search term.  The item does not have to start with the search term.

list-search-results-narrowed-down-as-you-type-data-validation-list

Once you have selected the item you are looking for, press Enter on the keyboard or press the Input Value button on the form to input the value in the selected cell.

enter-key-or-input-value-button-to-enter-value-in-cell-list-search

List Search works on any cell in any workbook.  There is NO special setup required.  Simply select a cell, press the List Search button, and start searching the list.

List Search Options & Features

The List Search Add-in contains some features that make it super fast to do data entry and work with your lists.  Press the Menu button in the List Search window to see the options.

list-search-add-in-options-sort-and-direction-settings

  • Select Next Cell – After pressing the Enter key or Input Value button, the cell below the active cell is selected.  This behavior can be changed in the direction drop-down menu.
    • Down – selects the cell below the active cell.
    • Right – selects the cell to the right of the active cell.
    • None – does not change the selection.
    • Close – closes the List Search window.
    • Paste – Copies the input value to the clipboard and pastes it to the active cell using the VBA SendKeys method.  The List Search Window closes.  This is the only option that retains the undo history in Excel.
  • Sort Order – The drop-down list can be sorted in ascending (A-Z), descending (Z-A), or original order by pressing the toggle buttons in the options menu.  This only sorts the list in the List Search window.  It does not sort the data validation list in the cell.
  • List Info – The Info button displays additional information about the drop-down list.  It currently displays the total number of items in the list.
  • Create List of Unique Values – A new button has been added that copies the contents of the drop-down list to the clipboard.  You can then paste the list to any range in the workbook.  This is a fast way to create a list of unique values when you use List Search on a cell that does NOT contain data validation.  You can also filter the list by typing a search, then copy the filtered list to the clipboard.

IMPORTANT Note: When inputting values to the active cell, the only way to retain the Undo History is by using the Paste option in the Select Next Cell drop-down list.  List Search uses macros to input the selected value, and macros typically clear the undo history in Excel when they modify the workbook.  The Paste option is a workaround that uses the SendKeys method to copy and paste the selected value.  This mimics what the user would do to copy/paste, and does NOT clear the Undo history in Excel.

Works on Lists Without Data Validation

List Search works on cells that do not contain data validation too.  If you select a cell that does NOT contain data validation and open List Search, the drop-down will be loaded with a list of unique items from the column of the selected cell.

list-search-works-on-cells-that-do-not-contain-validation

This is similar to pressing Alt+Down Arrow in a cell to see a list of values in that column.  However, the list does not need to be contiguous.  Even if the column contains blanks, List Search will still load all the unique values in the current data region or list.

November 2016 Update

I published an updated version of the List Search Add-in with a few new features.  Here is a video overview of the new features.

Here is a list of the features.

  1. Added a “Paste” option to the directions list.  This will copy the input value to the clipboard and paste it to the activecell.  The Paste options uses the SendKeys method in VBA to perform the paste.  This means the Undo history will NOT be cleared when using the Paste Option.
  2. Settings for the Options Menu and Input Direction Drop-down are now saved to the registry.  Your preferences will be saved and loaded when you open Excel and the add-in again in the future.
  3. Added enhancements for Excel Tables. When the activecell is in a Table and the cell does not contain validation, a unique list of values will be loaded and exclude the Table headers and total row.
  4. Added Copy List feature that copies the contents of the drop-down list to the clipboard. This feature is used to create a list of unique values from a column/table when the activecell does not contain validation. It also works when the list is filtered with a search term to only copy filtered results.

April 2017 Update

Based on your awesome feedback and requests, I'm excited to publish another update with new features.  I share the new features in the following video.

Here is a list of the new features in the April 2017 update.

  1. It added the Auto Open feature to automatically open the form when a cell that contains data validation is selected. You can toggle this option on/off with a toggle button in the options menu.
  2.  The add-in now works with data validation created by formulas (OFFSET & INDEX) and comma separated lists.  It should work with all types of data validation lists.
  3. Updated Escape Key behavior to close the List Search window.  If there is text in the search box, then Escape clears the search box.  If the search box is empty, then Escape closes the form.

In the video I also showed some cells with drop-down button icons next to them, even though the cell was not selected.  Check out my article on how to make the validation list drop-down buttons always visible to learn more about this technique.

Download the List Search Add-in (it's Free!)

The List Search Add-in is free to download and use.  The VBA code is also open source so you can modify it for your needs.  This is also a great way to learn how macros and add-ins work if you are learning VBA.

List Search Add-in Free Download 640x360

Click here to download the List Search Add-in

Note: You will create a free account for the Excel Campus Members site to access the download and any future updates.

The download site also contains installation instructions and videos.

How Can My Co-workers Use List Search?

The List Search Add-in is installed on your computer, and only you will be able to see the XL Campus tab and use List Search.  If you want your co-workers to be able to use List Search there are two ways to go about it.

  1. Send them a link to this page to download and install List Search on their computer.  They will be able to use List Search on any Excel file they have open on their computer.
  2. Import the List Search userform to the VB Project in your Excel file.  You can add the List Search form to any of your workbooks.  This must be a macro enabled workbook.  You will also need to create or import the code module that contains the macro to open the List Search userform.  Then add a button to the worksheet or ribbon that opens the form.  There is a video on the download site that walks through this entire process.  Once you opt-in to download the add-in you will receive a free account to access the Excel Campus Members Area and the download site.

How Can We Make List Search Better?

I hope the List Search Add-in saves you some time searching data validation lists.  The ultimate goal is to make it faster to find the value we are looking for in long lists of data.  Please leave a comment below with any questions or suggestions.  Thank you! 🙂

166 comments

Your email address will not be published. Required fields are marked *

  • Great Tool! I am starting to develop my own add-in to house all the macros I created for my colleagues, and I would love to add your list search as a part of mine or add my code to your add in, so they only have to install one and it’s all together on the ribbon. I am a beginner when it comes to add-ins, and I can’t seem to figure out how to move it between them. Is this possible?

  • Hi Jon,
    Thank you for the add in!
    I managed to see the ribbon for the “List search” Add In after trusting the location.
    But all I can see is the “List Search” Icon and Help page.
    I don’t see any other buttons. Am I missing something?

    In addition, when I click on a cell that is blank or has no validation list, there is a pop up saying “Please select a cell that is not blank or contains a validation list”

    However if I make the column contain a valdiation list and click “List Search” there is another error message “Run Time error “13” Type Mismatch” WhenI click Debug, it brings me to the form “f_ListSearch.Show”

    thank you!

  • that is an incredibly good tip and utility talk. What would make it even more useful would be if this could be used for pivot table selections e.g. selection could be made from using the search functionality and pass through to the pivot table. supercharged this would be if it could pass multiple selections based on a list in say another table

    Your thoughts?

    • Thanks for the suggestions Gavin! I like the idea for using List Search on the Filters Area of the pivot table. I will add that to the feature list for the future.

      In regards to multiple selections, I just posted a blog post and video on how to filter for a list of items. The COUNTIF technique can be used to add a column to the source data, then add that column to the Filters Area or slicer to filter for a list of items. It’s a bit of a workaround for what you are looking for but should work. I like your idea though. I will give it more thought.

  • I should add that the Run-time error 1004 is on line 3 of the following Sub:

    Sub btnListSearch_onAction(control As IRibbonControl)
    ‘Callback for ListSearch onAction
    f_ListSearch.Show ‘(Run-time error 1004 error here)
    End Sub

    –Andrew

  • Hi Jon,

    I downloaded and really like your plug-in, but receive the following error when I run it on a cell that contains data validation rules (it works on all other cells in the same sheet):

    Run-time error ‘1004’: Method ‘Range’ of object ‘_Global’ failed.

    I tried moving the form and modules to my particular workbook, but that didn’t help.

    My data validation is structured as follows: Sheet2 has the data validation values in columns, with the header row in each column specifying a category name, and the values in the remainder of the column specifying the valid values when that category is chosen. In Sheet1, column B allows the user to select a category, which is restricted to the column header categories in Sheet2. When the category is selected in column B of Sheet 1, the values in column A in the same row are restricted to only the validated values for that category specified by the list in Sheet 2. Each validation list is <250 items, with only about 15 categories total.

    I'm wondering if having the validation split between two sheets, or using named ranges in my data validation is interfering with your otherwise wonderful plug-in. I also have some very rudimentary VBA code in the workbook that duplicates the template sheet described above based on input from a userform, and clears the Sheet 1 column A value when the column B category is changed. I don't know if that would interfere with your plug-in either. Could you advise? Thank you for any help you can provide, and for the plug-in!

    –Andrew

    • Hi Andrew,

      Thank you for reporting this. Yes, there is currently an error with validation lists that are created with formulas. I’m working on updating the add-in to work with these cells as well. I will send an email out when it has been updated. Thanks!

      • Thank you for your response, Jon! I would love it if you contacted me when you have a solution for validation lists created with formulas. I am using a controlled vocabulary to manage user inputs, and a few of the validated lists are very long, so a find-as-you-type feature would save time. Additionally, Excel disables mouse wheel scrolling in the lists, and also the ability to exclude blank values, which are generated by having a blanket cell range point to the validated list so I don’t have to update it each time items are added. Those limitations in Excel make your plug-in particularly useful.

        –Andrew

  • Jon,

    I’d love to be able to point your tool to a list in one sheet, then work on another sheet, where I can leverage the list from the first sheet. How difficult would it be to provide a named range to f_ListSearch, from which list items could be searched, selected and inserted in a cell from a completely different sheet. Also, it might be useful to allow multiple items to be selected and dropped in a cell, separated by some preferred separator. That’s my list for Santa. Btw, your hear this a lot from others, that your tool is terrific. That praise is rightfully earned. I’m hoping my suggestions are viable and someday find their way into the tool. It would catapult, an already exemplary tool, to a much higher level.

    • Hi Frank,
      My apologies for not responding sooner. Thank you so much for the feedback and suggestions. Those are some really great ideas! Both are definitely possible. I will put them on the list for future features.

      Thanks again for your support! 🙂

  • Hello Jon,

    I must be missing something but when I add the add-in to my cart, go to my library and click on it there appears to be no way to download it?

    • Hi Stuart,
      You might want to try refreshing the page. You should see a link to the Download page towards the bottom of the page. Here is a screenshot.

      List Search Add-in Download Page

      Please let me know if you have any questions. Thanks!

  • Thanks for your add-in, it’s really helpful. In my case the add-in works perfect except when the validation list is defined using the “INDIRECT” function:
    Origin=INDIRECT(“Name”)

    I’m Spanish and I have excel 2010 installed in Spanish. Could be this the reason of the failure(Spanish function for “INDIRECT” is “INDIRECTO”?

    Thanks

    • Hi Jose,
      Thank you! I’m happy to hear the add-in is helping you. I need to update it to work with cells that use formulas in the validation. It does not currently work with any formulas. I will send out an email when the add-in has been updated. Thanks!

      • Hi Jon, Jose,
        I had the same problem, however if you build an array in a spare column using the indirect function and then define the validation list on this column then the add-in works. Thanks very much for your help Jon!

    • Hi Leon,
      Yes, this is possible. I have it on my list of features to add to the add-in. We need to use AppEvents to monitor the cell selection event in any workbook with the add-in.

      You could also move the userform and code modules into a specific workbook, then use workbook events to open the userform when a cell with data validation is selected. I will create an article/video with instructions on this in the future.

      Thanks!

  • Hi Jon,

    Thank you so much for this amazing tool! You have wonderful skills my friend.

    II do have a question or two. I’m looking to embed the tool in the workbook so that it can used by various colleagues of mine. The worksheet i have has over +50 cells that have data validation lists attached to it, mostly for the purpose of inputting information and saving it to another woksheet (my version of a mini-db). I do however have only one cell that has a data validation list that’s referenced from the Db itself and is used as a “search” function to pull information from the Db.

    I’d like to use your tool so that my colleagues can search each of the +50 input cells by entering keyword searches, including in the “search” cell. Is this possible without having to do a command button and selecting each cell that contains a data validation list manually??? I’d love for my colleagues to just directly start typing in the actual cell that contains the data validation list and have the proper information show up, and able to select it.

    If it’s possible, I’d be the office hero! 🙂

    Thanks for all your help!!

    • Hi Nelson,

      Yes this is possible by copying the userform and code modules to your workbook, then using Workbook Events to open the userform (List Search Add-in window) when the user selects a cell that contains data validation. I have received quite a few requests for this and will create a video on it.

  • Is it possible to change the location of the add-in.

    It keeps appearing in front of my cell.

    If me middle of screen would be perfect.

    Thanks

    • Hi Sanh,
      Yes, the add-in attempts to open next to the active cell, but there are a lot of scenarios where this can go wrong. I have this on my list of features to fix/add.

      You can modify the VBA code if you are familiar with VBA. On the forms Initialize event you can comment out the following line and add a line to change the startup position to 1 (the center of the screen).

      'Call Move_Form
      Me.StartUpPosition = 1
      

      I hope that helps.

  • I add the add-in to my workbook but every time I re-open the workbook I have to reload the add-in? Is there a way around this? Thanks

    • Hi Giulio,
      Yes, this is due to a Microsoft Office Security Update and can be fixed. Here is an article that explains the disappearing add-ins in more detail.. There are also instructions on the installation instructions included with the add-in. I hope that helps.

  • Thank you for that great add-in, you really helpped me alot, I’m very glad that I found it. This uitlity helped me to diminish the size of my excel sheet by 90%.

    I’ve one question please, when I press enter to insert the searched value into the selected sheet, the Numlock is turning off automatically.

    I’m using MS. Excel 2010 32 bit on windows 7 pro 64 bit.

    Thank you once again,
    Best Regards,

    • Hi Nicolas,

      Thank you for the nice feedback. I’m so happy to hear that the add-in is helping you.

      The NumLock issue is due to a bug with the SendKeys method that is used to copy/paste the value in the sheet. I tried to account for this in the code, but looks like it might not be working for you.

      I just added a new file to the download page that is a NumLock Issue Version. I just removed the line of code that normally turns the Num Lock back on. Give this version a try. There is also a page in the members area on how to update an add-in. You can follow these instructions to install this new version.

      Let me know if that fixes it. Thanks again! 🙂

  • Oh, I forgot to mention that there is a worksheet titled “Drop Downs” with multiple lists on it. Two of those lists are used on 10-12 of the worksheets and the rest are used by the individual department/division’s worksheet.

  • Great job!!! This is what I’ve been trying to do with a spreadsheet used by many different users. I’m not sure if they use shared computers or if each person has their own laptop. Is there a way to incorporate the list search without having to set macro to enabled? Company policy prohibits having the settings to enable macro and management does not like add-on without it going through the IT department for approval.

    • Hi Rena,

      For the add-in, each user will need to have it installed on their computer. The userform can be added to a macro enabled workbook, and it would travel with the workbook. Either way, you will have to enable macros to use the add-in. It is written with VBA macros. I hope that helps.

  • Hi Jon,

    really great tool! Very, very helpful for me. Thank you!
    One question: it si possible to take in consideration diacritics when you are searching?

    • Hi Simon,
      I’m sorry to hear that. Thanks for sending the screenshot. Which version of Excel are you using? Are you using Excel on a terminal server or any kind of shared environment? Thanks

        • Hi Simon,

          Thank you for sending the screenshot. It looks like you might have an older version of the add-in. I updated it on December 4th with code that should work with 64-bit. I’ve tested it on Windows 10 and it does work on 64-bit there. You can download the updated version by logging in to your account and going to the Download page for List Search. Here is the link.

          https://members.excelcampus.com/library

          There are also instructions there on how to update an add-in. Here is a link to that page.

          https://members.excelcampus.com/products/the-list-search-add-in/categories/142180/posts/460875

          Please let me know if it is still not working for you. Thanks!

          Let me know if that helps. Thanks!

          • it’s working now but there is a little problem is when i use paste the window closed but this note happening in the other options like right down…

          • Hey Simon,
            Glad to hear it’s working now. This behavior of the window closing is a limitation of the Paste option. This option uses the SendKeys method in VBA to perform the paste. It’s basically the same thing as you press Ctrl+V on the keyboard to paste. The advantage is that the undo history is saved. The disadvantage is that the SendKeys method can be tricky to program. In order to get it to work I had to close the form and not take any more actions like selecting the next cell. It can cause errors and paste the value in the next cell. A lot is dependent on processes running on the users computer, and that is impossible to predict. So for now, the only way to perform the Paste is to have the form close.

            You can add the List Search button to the Quick Access Toolbar, then use a Alt+Number keyboard shortcut to re-open it. Here is an article on how to create keyboard shortcuts for the Quick Access Toolbar.

            I hope that helps.

  • Jon,
    There are some really cool formula based solutions to this problem, but they require a lot of setup work for each validation list in your file. can you show us those formulas and setup requirements some day? If you ever do work on other PC’s knowing the long way sometimes pays off. I do want to thank you for your amazing relentless work and dedication to teaching excel better that most documentation does!

    • Thank you Sam! I agree that there are some really cool (and complex) formula based solutions. I will add these to my list for future articles. I try not to put too many complex formulas in my models because it makes it harder to pass on the maintenance to someone else, but it is great to learn the functions and techniques used within those formulas. Thanks again!

  • Hey Jon,

    When I try to use the button to copy the list of drop down options to the clipboard I get an error “memory location could not be unlocked. copy aborted”

    Happens every time with any list. Any clues as to why that would be so?

    Best regards

    Jim

    • Thanks for reporting this Jim! It looks like you are probably using 64-bit Excel. I think I found the fix, although I have not been able to test because I don’t have a machine with 64-bit right now. I updated the add-in file in the downloads area. Can you download the updated file and let me know. There are updated instructions on the site as well. Thanks!

  • Hello Jon,

    I tried to download the Search add-in, but my e-mail is rejected and even trying “forgot your password” fails.

    Best regards. Hervé.

    • Hi Herve,

      I’m sorry to not get back to you sooner. I just created an account for you, and you should receive an email with your username/password to the Excel Campus members site where you can download the add-in.

      Thanks again!

  • Hi Jon,

    Thanks for the Add-In. This helps immense with large validation lists. In the past I always decided not to use validation lists if more than seven to ten entries would have been necessary. This might change now.

    But I am afraid your macro crashes if Application.ReferenceStyle = xlR1C1.
    You would need to convert the formula.
    The following code helps if you insert right after the following line the code of the form:
    If sFormula “” Then ‘Cell has validation list

    Insert below:
    If Application.ReferenceStyle = xlR1C1 Then
    sFormula = Application.ConvertFormula( _
    Formula:=sFormula, _
    fromReferenceStyle:=xlR1C1, _
    toReferenceStyle:=xlA1)
    sFormula = Replace(sFormula, “[” & ActiveCell.Worksheet.Parent.Name & “]”, “”)
    End If

  • I’ve been trying to create something like this off and on for a while. Much appreciated.

    I do have one issue I’d like to bring to your attention. I am using four monitors. When I have a spreadsheet on a monitor other than my ‘Main Display’ the form does not appear at the spreadsheet. Instead, it appears at the appropriate location, but on my Main Display. I think the Move_Form() controls this, but not sure if it can be modified to account for multiple displays.

    I was also trying to modify the code to accept the TAB key to do the same as the down arrow. I added ‘And KeyCode 9’ to the list, but that didn’t do it. I’ll keep looking. it may not be possible.

    • Hi Brewmaj,

      The Move_Form macro does control the location that the userform appears. It is very basic code, and does not really account for multiple monitors. Chip Pearson has a Form Positioner macro that is much more advanced and will probably handle these scenarios much better.

      The TAB key can be tricky because it is also used to select the next control in the userform based on the tab order. I hope that helps.

  • No bad comment. Only thank you so much. If I got it last week I will for sure do my test much better…. Great Job…..

  • Looking at the preview, it sure does look awesome. Kudos! =)

    I have to wonder about being able to search cell formatting though. I color code various things in my table in order to filter for them. (for ex I use different fill colors to denote different job types, and a common font color to tie the open work orders together. Everything else would be a normal/clear format. I would use bolds or italics too, but you can’t search for those… that I know of.)

    For that matter, is it possible to do multiple filter/searches? Heehee, sorry, we ask for so much! =P

    • Hi Carrie,

      You can search for formatting using Excel’s built-in Find Window (Ctrl+F). Here is a screenshot of the Find Window. Press the Options button to display the Format.. button on the find window. From there you can set the formatting properties you are looking for by pressing the Format… button. You can also select the Choose Format from Cell… option from the drop-down to select a cell with the formatting properties you are looking for.

      Find Cell Formatting in Excel with Find Window Ctrl F

      I hope that helps answer your question. I will create a video on this in the future. Thanks!

  • Very Nice Work!

    Just to help you improve this, I would change the function ShowSearch to below so that when there is no active worksheet, the code would not break:

    If Not ActiveSheet Is Nothing Then
    f_ListSearch.Show
    Else
    MsgBox “An active worksheet is required to use this feature.”, , “Active Worksheet Required”
    End If

  • I have a question regarding “Paste”; if I understand correctly, if I choose “Down” or “Right”, the add-in places the value in the cell and then moves down one or right one. Am I right that this is a Paste operation, followed by a move down or to the right? Is it that second action, the move, that clears the Undo history, hence the reason for the “Paste” option? I guess I’m confused because it seems to me that at the user level all three options, Down, Right, and Paste, all paste the values in the cell, the only difference is where the cursor goes next.

    • Hey Jomili,

      Great question! Sorry if the explanation was confusing.

      The undo history is cleared when the add-in inputs the selected value into the cell. When VBA code makes changes to the worksheet like this, then the undo history is cleared.

      The undo history is not cleared when VBA code selects a cell. So the issue is the code that inputs the value.

      As a workaround, we can use code to copy the input value to the clipboard, then mimic the Ctrl+V keyboard shortcut to paste the value to the selected cell. This is what the “Paste” option does, and it uses the SendKeys method in VBA.

      You will also notice that the form closes after hitting the input button/Enter key, when using the “Paste” option. The selection does NOT change after the value is pasted. This is because the SendKeys method is somewhat unreliable. It works differently on different computers and setups. So to keep it as simple and reliable as possible, the code does not take any other actions after performing the paste.

      Therefore, I put the “Paste” option in the same drop-down menu as the directions. It would be great if we could paste the value, then move down or right one cell. I originally had it setup to do this, but my tests were not consistent. My Paste Buddy add-in does something similar with SendKeys, and I’ve experimented a lot with getting it to work. However, I’m definitely open to suggestions.

      If you add the List Search button to the QAT and use a keyboard shortcut to open it, then it should be pretty quick to perform a “Paste”, move down or right with the arrow keys, then re-open the form.

      I hope that helps answer your question. Please let me know if you have any other questions. Thanks!

  • This is a fantastic add-in and thank you for making it free! It has already saved me lots of time searching my huge lists of data! Looking forward to learning more from your site. Keep up the great work!

  • Is it Christmas already?! I can’t believe you’re giving such a useful tool away for free. Plus, I get to study your VBA code. Thank you for sharing this. I’m really looking forward to installing it on my office laptop tomorrow.

    • Thanks Mark! I feel like this is one feature that should be built into Excel, and that a lot of users can benefit from. I will be releasing an update this week with a few new features as well. I hope you enjoy it. 🙂

  • Hi Jon,

    Love the add-in! Thank you very much. One thing I noticed is the setting for “Select Next Cell” doesn’t hold after I close and reopen the workbook. It defaults back to “Down”. I suppose I could add a variable somewhere in the code to hold the setting but I thought you might consider adding it to your add-in.

    Thanks again for a great solution,
    Michael

    • Thank you Michael! I will be releasing an update that fixes that problem, and has a few new features. Everyone that downloads the add-in will receive an email when the update is available. Thanks again for letting me know. 🙂

      Jon

  • Hi Jon,

    Thank you for the useful add-in and an excellent video that goes in real details and explains in non-technical terms. Appreciate it.

  • I really like your new addin. I also like the Tab Hound video link. Is that avaliable as a free addin?
    Is there a way to copy the unique list of choices from the list search when it’s not part of a validation list? If I have 1000 rows of names and only 30 unique items in the list, I see your tool presents me with the unique names but now I’d like to just copy the list of 30 without having to copy the whole list and then remove duplicates to arrive at the unique list. Is this possible?

    Thanks a bunch.
    Barb Cardenuto

    • Hi Barb,

      That is a great suggestion! It does not currently do that, but can easily be added. I have a few more changes I want to make, and will publish an update to it with that copy list feature as well. It would be a lot faster than using the Remove Duplicates feature on the Data tab.

      Tab Hound is a paid add-in. Here is a link to the Tab Hound page that has more info. I also added a new Table of Contents Gallery feature to Tab Hound last week. It’s a great way to spice up your workbooks. 🙂

      Thanks again for the great suggestion! I will send out an email to everyone that has downloaded List Search when it is updated.

  • Jon

    thank you for this it looks great
    been looking around the internet for ages trying to build something like this when I should have come straight here

    thank you

    James

    • Thank you James! I’ve been wanting this feature for along time too. The idea to create this solution popped into my head on Sunday, and the List Search add-in was born… 🙂

  • Hi.

    I have been trying to do something like this for a very long time, so very many thanks.

    Can you please tell me how to access it using my keyboard? I would prefer not to have to keep going to the “XL Campus tab” and clicking the “List Search”. For me the utopian would be to type a couple of letters in the cell, then press a “function key” or a “Ctrl-character” and it would then insert what I had typed into you form and find all the matches.

    Even using the Quick Access toolbar would better – I know how to assign macros to buttons on there, but not VBA addins.

    Even if there is no way to do what I have asked, I am sure I will use it a lot as I do use a lot of drop-down lists

    cheers

    Harvey

    • Hi Harvey,

      Great question! You can right-click the List Search button and select “Add to Quick Access Toolbar”. Then use the Alt+Number Key keyboard shortcuts to open List Search with a keyboard shortcut. Here is a video that explains how to do this with my Tab Hound Add-in. The process will be the same with List Search.

      Keyboard shortcuts can also be programmed into the add-in to open the form. I might add functionality for that someday. But the QAT is the quickest way to open with a keyboard shortcut right now. Let me know if you have any questions. Thanks!

      • Wow – I really was not expecting a reply so quickly – much appreciated. That worked perfectly, and I am pleased to see that I can also use F4 to close the form – so my mouse is not required – wonderful 🙂

        I now have a lot of icons in my quick access toolbar – I am using “MS Office Professional Plus 2016”. Is there any way that I can create my own icons, &/or include short descriptions alongside the icons in the Quick Access toolbar?

        Many thanks

        Harvey

        • Hi Harvey,
          Thanks for the F4 tip on closing the form. That is going to be Alt+F4 for most users. The Escape button clears the text box, so that is why it doesn’t work to close the form.

          You can add your own macro buttons to the QAT, by choosing Macros from the Commands drop-down in the customization menu. You can then change the icon for the button and modify the display name by clicking the Modify button.

          I hope that helps. Thanks again!

  • Hi John,

    I love your blog and what’s impressive is your teaching methods, simple and precise.

    The only complain is that I would like you to blog at least 3-4 times a week.

    Cheers, Manny

  • Hi Jon,
    I’m eager to download List Search and give it a run. However, I’ve encountered a use I think your add-in addresses, but I can’t be sure from the description. I had a project a few months ago for a software company with an accounting and project management application used in the construction industry. The Excel complement I was working on pulled employee names from the application via ODBC and made them available in validation lists on an Excel form populated by field managers. The field managers usually didn’t have remote access to the home office’s database.

    My validation dropdown didn’t have the google-like search capabilities of your add-in, so that’s an enhancement that would have been appreciated. But there was something else they wanted that I think List Search may allow.

    The field managers often hired people at the job sites who weren’t in the company system. They therefore needed to be able to type names into cells where the form enforced validation. To accommodate that I wound up leaving a few rows on the form without validation, but even that was less than ideal. The Excel form had to mimic a paper form required by the state, and the number of rows was limited.

    Toward the end of your write-up you say “List Search works on cells that do not contain data validation too.” If that’s the case, am I right in assuming I could leave cells (rows) on my form without validation so that field hires could be typed in and those same cells could still be used as if they’d been pre-set for validation only?

    I really appreciate all your work, especially the training courses.

    Thanks,
    Rick

    • Hi Rick,

      Great question! Yes, the List Search Add-in could handle the issue of adding names to a cell that contains data validation, but does not contain the new name.

      When the value is entered in a cell through List Search, it is being input with VBA code. When inputting values into a cell that contains validation with VBA, the validation rules do not apply. That means you could input a value in a cell that contains validation and break the rule, and a warning message/error will not appear. I built some basic validation into List Search to handle this, but you could take that code out. The Input_Value macro in the f_ListSearch code module has a section labeled “Validate Entry”. You can just replace that code block with

      bExists = True

      This will prevent List Search from checking to see if the input value in the form exists in the data validation list.

      You might also want to add some code to add the new name (input value) to the validation list source table or range. That way the new name is automatically added to the list for future use to prevent typos. That code could be added to List Search with a macro that is called in the Input_Value macro.

      I hope that helps. I have developed headcount rosters on budget templates like this before, so I understand the process. There are definitely a lot of possibilities with this tool and I’m excited to hear everyone’s ideas. Thanks again!

  • Hi Jon, If in the sheet1, A1=Current Date, B1=Apple, guava, banana (from drop down menu), C1=Price, D1=Seller. When I shall input data in sheet1 (Only in B1, C1, D1) and those will be replaced but stored in Sheet2 (for Apple), Sheet3 (for guava), Sheet4 (for banana) according to date. Is it possible?

  • Hi Jon,

    I love the idea of this addin. The problem that I am having with it is that when the userform for the tool opens, it is not wide enough to get to the options such as the sort option or what to do when the enter key is pressed.

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly