Creating a Searchable & Multiple columns in a drop down list

[inddon]

Hello There,

I need your help in creating a drop down list (without any activex controls or combo box) with the following functions in it:

1. The drop down list to be a combination of multiple columns:
a. Display: Concatenated columns B & C plus individual columns D, G
b. Output: The returned value in column 'Parent Key' will be the selected concatenated value from the drop down list.

2. It should be a searchable drop down list i.e. when a user types a part of the text in a name then the drop down list should display only those names which the user has typed in the drop down list.

Eg. a. User entered text in drop down list = John
b. All names which contains John should be displayed in drop down list

3. The drop down list will be copied in all the cells in the column (eg. I11:I20)

I have attached the excel file for your reference.

I am using Excel 2010. To me it seemed a bit difficult, but I am sure someone must have already tried this solution, and I look forward to hearing from you.

Thanks & regards
Don

 

[jeffreyweir]

This would require VBA, or some array formulas which you would have to enter over a range large enough to hold the largest possible number of results (and that would be very inefficient).

You can achieve pretty much the same thing just by using a PivotTable. See attached image.

 

[inddon]

This would require VBA, or some array formulas which you would have to enter over a range large enough to hold the largest possible number of results (and that would be very inefficient).

You can achieve pretty much the same thing just by using a PivotTable. See attached image.

Hello Jeffrey,

Thank you for your reply. I just came across a YouTube video for point 2 i.e. searchable drop down list.

I would like to do it without pivot table, as the user is not that familiar with it.

Somehow I am looking how I can best bring these things in a more user friendly way.

Please let me know, any help appreciated.

Thanks & regards
Don