Auto sort of table based on a cell value

[Bill Morris]

Hi

I am trying to create a mulit stage set of drop downs to find an overall Material Type - Supplier - Material Name.

The second dropdown being dependent on the first, third on the second.

To save having many dependant lists I want to create a table on sheet 1 with, say 3 columns.

Column 1 - Material Type
Column 2 - Supplier
Column 3 - Material Name


Sheet 2 I want a series of 3 dropdowns

Cell A1 - Dropdown 1 - I select a Material Type, this then determines the dropdown options in the second (cell A2), and so on.

Note - there is a many-many relationship.

Various materials can be supplied but various suppliers, and vice versa.
Supplies supply a range of materials with different material names.

Please help!

 

[MandeepBaluja]

Excel Dependent Drop-down Lists - Easy Excel Tutorial

check this out.

Regards,
Mandeep Baluja

 

[Bill Morris]

Wow...that was quick.

I'll check it out. Thank you!

Just checked it. I've seen that one before. Works great with 2 dropdowns but when I add a third it can get very complex.

I would have to have a list for every item in dropdown #2..this could be 30+.

Maybe there is some vba code to autofilter the table based on selections made in the dropdowns?

 

[MandeepBaluja]

Try this I have created this in hurry

Sub Dependend_Drop_Down_List()

'First Keep your Data like this in these column From which you want a dependent Dropdown.
'----------------------------------------------------------
'A ------B
'Data Material
'Fruit mango
'Fruit banana
'Fruit apple
'liquid alcohol
'liquid vodka
'liquid beer
'Solid Table
'Solid chair
'Solid bottle
'----------------------------------------------------------------------------


'For Example your data is in Range("A1:B10") So "A1:A10" would be your first dropdownlist on their basis you will manipulate dependent dropdownlist
Range("A1:A10").Copy Destination:=Range("K1")
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

'Remove the Duplicates
Range("k1:k" & lr).RemoveDuplicates Column = 1

'named the range first
ActiveWorkbook.Names.Add Name:="Items", RefersTo:=Range("K1:K" & lr)

'First Drop Down list will be like
'Remove unique from list and create a dropdownlist

With Range("M1").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Items"
End With

'You have to named ranges automatically that corresponding/Dependent upon the first drop down list

For i = 1 To lr
'Run an autofilter to get the values corresponding/Dependent upon the first drop down list
ActiveSheet.Range("A1:b20").AutoFilter field:=1, Criteria1:=Range("K" & i).Value

'use new sheet blank columns to create named range

ActiveSheet.Range("b2:b" & Cells(Rows.Count, 2).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Sheets("sheet2").Cells(1, i).PasteSpecial xlPasteAll
ActiveSheet.ShowAllData
lr = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
ActiveWorkbook.Names.Add Name:=Range("K" & i).Value, RefersTo:=Sheets("Sheet2").Range("cells(1,i),Cells(lr,i)")
Next

'After naming ranges automatically Create second drop down which is depened upon first drop down

With Range("I15").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=" & Range("K" & i).Value
End With


End Sub


Regards,
Mandeep Baluja

Wow...that was quick.

I'll check it out. Thank you!

Just checked it. I've seen that one before. Works great with 2 dropdowns but when I add a third it can get very complex.

I would have to have a list for every item in dropdown #2..this could be 30+.

Maybe there is some vba code to autofilter the table based on selections made in the dropdowns?