Multiple dropdown list

[elsuji]

Dear Team,

I need to make the drop-down list on sheet2.

There is 3 column EQ GROUP, EQ SUB GROUP, MODEL NO in sheet 2. The data's will taken for this drop-down from sheet "Equipment group".

For example,

In EQ GROUP if i select "CONCRETE MACHINERY" then in EQ SUB GROUP the following only to display "Batching plant, Recycling Plant, Concrete Pump, Boom Placer, Separate Placing Boom, TSM, Truck Mixer".

And In EQ SUB GROUP if i select "Recycling Plant" then in MODEL NO the following only to display "RA12, RA6"

Like above only the selected products to be display on the drop-down list.

The data file is attached with this mail for your reference.

Can any one help, how to do this with VBA code

[p45cal]

See attached.

Isn't your MS Office version by SPecialiST RePack a pirated version of MSOffice?

[elsuji]

Hi

Thanks for your reply. It is working as per my requirement

[elsuji]

I have one more doubt. At present the formula is applied only on 2nd row. How can we apply this formula on entire column

[SamT]

See Attachments. This is the way I always structure my lists when I have multiple interconnected lists boxes.

UserForm code

Dim ListColumn As Long 'Module level variable

The List for ListBox1 is always

UserForm_Intialize()
ListBox1.List = ListSheet.Range("A1").CurrentRegion

The ListBox1code is always

Dim ListHead As Range
Set ListHead = ListSheet.Rows(1).Find(TextBox1.Value)
ListBox2.List = ListHead.CurrentRegion
ListColumn = ListHead.Column


ListBox2 code is

Dim ListHead As Range
Set ListHead = ListSheet.Columns(ListColumn).Find(TextBox2.Value)
ListBox3.List = ListHead.CurrentRegion

If I need to use 4 deep lists, I Add List sheets, and keep a List of Listsheets on the UserForm Code page. This technique makes it easy to Add, Delete, and edit lists

[p45cal]

Copy down the data validation in cell C2 as it is.
Change the data validation in D2 from:
=INDIRECT(SUBSTITUTE(SUBSTITUTE($C$2," ","_"),"-","_"))
to:
=INDIRECT(SUBSTITUTE(SUBSTITUTE($C2," ","_"),"-","_"))
Then copy down.

Change cell E2 data validation from:
=OFFSET('Equipment group'!$A$3,0,MATCH($D$2,'Equipment group'!$B$2:$AC$2,0),COUNTA(OFFSET('Equipment group'!$A$3,0,MATCH($D$2,'Equipment group'!$B$2:$AC$2,0),28)))
to:
=OFFSET('Equipment group'!$A$3,0,MATCH($D2,'Equipment group'!$B$2:$AC$2,0),COUNTA(OFFSET('Equipment group'!$A$3,0,MATCH($D2,'Equipment group'!$B$2:$AC$2,0),28)))

[elsuji]

Dear Sam,

I dont want to use the user form. I just want to use with Data validation

[paulked]

Well, the goalposts move yet again!


Can any one help, how to do this with VBA code and then


I dont want to use the user form. I just want to use with Data validation

Here is a VBA solution 25490

BTW, I can't condone pirated software, especially when it is peanuts a month for 365.