Dynamic Combobox searchfucntions 'Indirect' dropdownlist (VBA) (MS Office Excel Pro 2016)

Hi Forum,

I'm kind of stuck with this issue for 2 months now. I'm trying to create a userfriendly searchfunction for my food database which consists over 10000 item lines.
I already have created defined names, and created 2 columns with a few dropdownlists that indirectly link the data chosen in the first dropdown, so the coresponding next data will show in the next dropdown. The lists are still too long, so a decent searchfunction is needed. I figured the only way in excel for this to happen is with a combobox, so i got a combobox now with some additional scripts to convert the 'indirect' function from datavalidation to a vba readable version.
So what's not working yet?
1st. - when i type in the searchbox it will only show the first looked up searchresult in the combobox dropdown, only after pressing enter and reselecting the combobox with the remaining searchword in it, it'll show the full searched list.

2nd. - Is it possible to filter the dropdownlist either in the combobox or both in the combobox and the normal cell in alphabetical order without having to sort my actual datatable?
In the attachement there is a link for an example workbook with a similar simulated problemcase and vbascripting.


Thanks in advance for the help.

Kind regards,

Arnoud Holtzer

Welcome to the Forum!

An image attachment has very little value. Just attach the Excel file. It's easier than taking a screenshot first and then attaching that.

It will be much easier to understand your problem if you provide your file. This allows us to see and experiment with your data, layout, formulas, code, and possibly attach a file with a completed solution. If you are looking for formulas to produce a desired result, it helps if you create a mock-up of what you want the result to look like. Otherwise we would have to build something from scratch, trying to guess what you want it to look like.

The paper clip icon does not work for attachments. Instead, under the text box where you type your reply click the Go Advanced button. On the next screen scroll down and click on Manage Attachments, which will show a pop-up window to Select and Upload a file. Then close the window.

Hi Jeff,

Thanks for the reply,

I had the message for not being allowed to place external links. Now i got the workbook uploaded as attachement in the first post.
I hope this will help in getting towards a sollution. If more information or action is needed please let me know.
The screenshots where to help show the action taken to simulate the 1st dropdownissue.

Kind regards,

Arnoud Holtzer