So I have this problem where I want to populate data in combobox where it has reference data.

Here is my table from excel:

And here is my form:

So when the user will input 100 in textbox like in the image above, only alex herd and Diana Edmunds will be available for dropdown and selection in Party Name combobox. How to achieve this?

CodePudding user response：

As you didn't add own code, I content myself only to the core facts of your post demonstrating an approach using the dynamic array features of MS 365 via the Filter() function, organized in a Let-function container which allows a clear structure avoiding some redundancies and enables to define expressions ("variables") together with their contents in pairs of arguments.

Needed steps

You are referring to an existing data sheet (assuming columns A:C), so try the following steps:

• Write your textbox value into a named cell (e.g. $D$1, named "Number"),
• Enter the following formula into cell $D$2:

=LET(data,FILTER(A:B,C:C=Number), FName,INDEX(data,,1), LName,INDEX(data,,2), PROPER(FName& " " & LName))

This formula writes the wanted names dynamically to a so called spill range which can be referenced by a "#"-suffix (e.g. by D2#)

• Eventually you can use the TextBox1_Change event to assign the entire data set to the .List property of your combobox:

     ComboBox1.List = ThisWorkbook.Worksheets("Data").Range("D2#").Value ' << change to needed sheet name