Searchable DropDown Menu from a Dynamic Range

  • The problem is a part of a larger project that utilises an assortment of APIs that retrieve data from a database and paste them as required into respective columns (this part is done), the next part is to get a searchable dropdown cell/combo box ( basically anything except a user form or anything like that which takes the focus out of the excel window) that shows the user (as they type) queries that match from the respective column (populated as mentioned earlier by the APIs) from which this cell is to be validated. The matching should be able to match from not just the starting of the query but other parts as well ie,


    If User were to type 'app' -

    entries such as 'app'le and pine'app'le should pop up in the dropdown


    Furthermore Search shouldn't be Caps sensitive either

    Main Problems faced from earlier ideas

    1) I checked google (and youtube) found a few solutions that go along the line of using multiple Formulas such as Search, Max, IsNumber etc (

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.
    ), this creates problems as the

    i) This option is not dynamic (new rows added to data validation column do not reflect)

    II) The Data validation Column is quite big (6000 rows+) and significant lag can be observed


    2) This option seemed closer to success as it doesn't slow the system as much but it uses VBA User Forms and also is not dynamic ie. new entries to Data Validation column do not reflect (

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.
    )


    3) Some cells additionally also require an option to allow for multiple selections from the drop down (Ideally separated by a comma so that I can directly parse it to the POST API that sends the data to the database)


    4) Some other cells may require an option to allow for entering Strings that have no match as well (Basically disabling the error prompt in traditional Data Validation)


    - Point 3) and 4) of the problem that can be addressed after the Initial problem is solved :saint:

  • Hi,


    If you load an example workbook, with examples of data type and sheet layout, and expected result, it would be an easier solve.


    Why do you not want to use a form? you can have the form return to the page after selection for example.

  • The example Workbook is uploaded here,

    Data type is always Strings
    Expected Results have already been mentioned in the original Post, ideally typing in the dropdown cell/combo box should search through the database for matches (not only just the beginning but through out the string of the database entries) ie.

    If User were to type 'app' - entries such as 'app'le and pine'app'le should pop up in the dropdown


    Here as we can see app is in the middle of Pine'app'le, it is still being picked up by the dropdown suggestions
    Also matches should be Case insensitive

    Searchable Drop Down.xlsx


    Lastly the reason for not using User forms is as the last sheet of the Example Workbook shows - the user visible sheets are already very well designed and uses a combination of autoshapes and images to provide a very intuitive and for a lack of a better term 'appealing' UI/UX and I personally feel the best way to provide this dropdown without taking away from that is to use Combo boxes.

    I already have macros to pass information from the Autoshapes to cells/variables in vba and parse the information to and from the database that finally keeps track of the information as well.

  • Forgot to mention this again in the previous reply, the number of rows in the Database Entry Sheet - Column A will keep changing as it is updated from the database every time a significant event happens (such as when a new item may be added to a client's inventory, every time the workbook is opened etc) so it needs to be a dynamic range and should thus reflect all the latest entries in the. drop down menu as well...

  • Hi,


    I have used the technique outlined here:

    External Content www.youtube.com
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.


    I used a different method to make a volatile range than is described in the video, I also tested by expanding the list of products to over 6000 and the response is instant.


    The only issue that I know of which might be a problem is that it depends on how recent your version of excel is as to whether the functions are available.


    Let me know how it goes:

    Searchable Drop Down JD edit.xlsx

  • I already saw this video as well and the same issue that you mentioned is the main problem as this workbook will be distributed to clients that may be using upto Excel 2003/2007 and the function mentioned works only with 365.

  • Unfortunately I think you are going to require a macro solution probably with forms, I have seen this question a lot and I am not aware of a solution other than that presented here. Maybe one of the mods knows a solution for it but I think that is where it will wind up.

  • I am aware of the macro requirement, but wasn't able to do it myself. At min if someone can guide (or upload a version) of the following video's logic such that I can use it within a combo box (or cell) directly then that would be great as well -

    External Content youtu.be
    Content embedded from external sources will not be displayed without your consent.
    Through the activation of external content, you agree that personal data may be transferred to third party platforms. We have provided more information on this in our privacy policy.

  • Hi,


    This is not as complex as the example in your video, it might do what you are after.


    Click on B2 to activate the form, there is also a beforedoubleclick or a beforerightclick option in the code for the sheet if you want to try them. Just drag the pice of code from its current position into the others to try them.


    In the form type in the textbox to change the fill for the listbox.

    Searchable Drop Down JD edit.xlsm

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!