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 (
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 (
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