Multi-functionality for data validation list - Dependent & Search Suggestion for a picklis

[laansesu]

Dear All,

Attached excel for your reference.

Sheet Receipts UF has Item Pick-list & Model Pick-list in which Model Pick-list is a dependent pick-list & I even required Search Suggestion for this drop-down list as this list will keep on increasing

So please help me in adding this search suggestion with formulas to this dependent Drop-down list.

Ex: As of now, it works like when I select Down-Rod in Item Pick-list, I get only concerned list in the Model Pick-list. Whereas I want to add one more functionality for this drop-down list i.e., When I start typing the words in the model pick-list, whatever the model has these words should appear in the drop-down list

Another request is that I need this to happen only with formulas, as I'm not much familiar with VBA

[laansesu]

Hi Everyone,

Could someone please reply on the above description

I only required 2 functions for one of the 2 data validation picklists as mentioned below in the above attached excel

1. Dependent Picklist - I've made it on my own. 2nd Picklist will display the values based on first picklist selected value. There is nothing to do with this.

2. Search Suggestion - I required someone's help in doing this. Above said dependent picklist should show the results based on typing words same as search in Google ex: If I start typing 9" this picklist should start showing 9" CG White, 9" CG Ivory etc in this dropdownlist.

[LeoTaxi]

made something in E5
only possible with helpers, those are columns Q R and S
and a little vba code for combox in E5
and a named range model2


Kind regards
Leo

[laansesu]

Everything is fine but only 2 things are making me to reply your post rather than making this post marked as resolve

Apart from the below 2 points, I've a question i.e., Cant we do this only with formulas rather than VB so that I can check it out for any issues rather than approaching you

1. Displayed results are unable to select with keyboard but can be done with mouse i.e., After typing the required model, Combo box is making me to select each and every model with mouse rather than keyboard

2. I'm getting debug at below code, when I'm entering the quantity

If item3 <> "Production" Then
r = Application.Index(ws2.Range("A1", "BK81"), Application.Match(item2, ws2.Range("A:A"), 0), Application.Match(item, ws2.Range("A1:BK1"), 0) + 1).Address
End If

Please resolve the above issues and do the needful.

[LeoTaxi]

Can not change the way it works and for the debug in code
i think you dont use the right date, in sheets there is only september
so a date in october wont work.

Kind regards
Leo

[laansesu]

My Objective is to route the quantity to different sheets based on the selected date, selected Item Picklist, selected Combo Box Value. But I found difficulties are selecting the required Model in the picklist for which I thought to minimize the displaying values by using these dependent picklist & search suggestion

Even attached updated where I've changed the date in all sheets but still getting the same error

[LeoTaxi]

the models has to be in the sheet also
for exemple Thermacole model New KDK is not in the sheet, that gives a error
for models present in sheet it works

Kind regards
Leo