I want to create dropdown lists in excel, it should work as follow: In cell2,1 must be a drop down list with 4 names (e.g. Mill, Comp, Loads, Blending), if I select say Mill then in cell2, 2 a dropdown list with all the mill parts must appear, same for the other if I select one of the other names their parts list must appear. Can anyone show me how to do this?
On a new sheet (let say sheet2) in Cells A1, B1, C1 and D1 enter values Mill, Comp, Loads, Blending respectively
Put the values of Mill, Comp, Loads and Blending under its respective column heading (make sure no blank line)
Go to Formula Tab on menu
Select Cells A1:D1
Click on define Name
Enter the name, lets say "MyMainList" and click Ok
Select all the values in col A, starting from A2 and click on name, give it name "Mill"
Select all the values in col B, starting from B2 and click on name, give it name "Comp"
Select all the values in col C, starting from C2 and click on name, give it name "Loads"
Select all the values in col D, starting from D2 and click on name, give it name "Blending "
Go to main sheet (lets say sheet1) and select cell A2
Go to Data tab on menu and click on Data Validation
Click on the drop down titled "Allow" and choose List
In Source box write =MyMainList
Optionally go to input tab and error tab to enter messages for that
Double click on "sheet1" in the project explorer
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Dim mainsrc As String
Dim subsrc As String
Dim lThisRow As Long
lThisRow = Target.Row
If Cells(lThisRow, 1) = "" Then
mainsrc = Cells(lThisRow, 1).Value
On Error Resume Next
subsrc = Cells(lThisRow, 2).Validation.Formula1
On Error GoTo 0
If ("=" & subsrc = mainsrc) Then
Cells(lThisRow, 2) = ""
With Cells(lThisRow, 2).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=" & mainsrc
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "This is my Input Title"
.ErrorTitle = "Oops Error"
.InputMessage = "Select a Value"
.ErrorMessage = "Not a valid value"
.ShowInput = True
.ShowError = True
Application.EnableEvents = True