Linking drop down boxes

in the source box where you put the range for the second drop down selection, you can use an if statement to set up multiple lists based on the first drop down box

Name a Column range that contains only the States. Then build a column for each State and name the column of Cities in a State by the State name.

Then build a list with the source being the name of the State list. When a user selects a State from this list it becomes the source for the City cell dropdown. This code will build such a list. The State list is in B1, the City list will be in D1, if you have created the named ranges for the collection of States and individual lists of Cities contained in a State, each named the State.

Sub BuildList()
'By Joe Was
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End Sub

This code is designed to run each time it is needed by the user and is placed in a sheet module. It could be converted to an envent macro so each time the State cell value changes it will run automatically?

I did not have time just now to play with that version of the code though! JSW

This code works as above and it also gos into the sheet module, all the conditions are the same as for the other code. This will automatically change the list based upon the state entered in B1, updating the list in D1. Given the named ranges are correct. JSW

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("B1")) Is Nothing Then
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
Else
myState = "=" & Worksheets("Sheet1").Range("B1").Value
With Worksheets("Sheet1").Range("D1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:= _
xlValidAlertStop, Operator:=xlBetween, Formula1:=myState
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = False
.ShowError = False
End With
End If
End Sub