1st Drop Down List Controls Options in 2nd Drop List

[Lazaro]

In the first box I want to select either the English or metric system then in the second box you either get inches, feet, pounds, etc. or centimeters, meters, kilonewtons, etc. depending on the selection that is made in the first box.

[danam531]

You can do an offset formula. Make the 1st combo box be either english or metric, then use that cell link to do an offset formula. have one column with all your english, the next with all your metric...in the next column do an =OFFSET(cell to the left of your first english entry, 0, CELL LINK FROM 1st combo box)

[IML]

list all you english items in a range and name it English via the name box. Repeat for Metric, naming it metric.

assume your english / metric drop down is in a1. For validating your new list, choose the list option and type in =indirect(a1) in the source box. good luck

[Lazaro]

Thanks. They both work like a charm.

[philipjsmith]

On 2002-05-22 13:00, danam531 wrote:
You can do an offset formula. Make the 1st combo box be either english or metric, then use that cell link to do an offset formula. have one column with all your english, the next with all your metric...in the next column do an =OFFSET(cell to the left of your first english entry, 0, CELL LINK FROM 1st combo box)

[Joe Was]

You can also do this with a drop-down list, if your values are hard coded in a lookup list. The code below will use one list to lookup values in another list.

The first code is manual the second is automatic.

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



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

To use the code!
Select an item in the dropdown in B1 (Like the: "State")
H column contains the list names for the lists in columns I, J & K, H would be the State list range name for the City list in column I, J & K or more column?
The list selected in B1 will set the dropdown values of D1 (Like the "Cities" in your selected State")

So you have one dropdown in B1 and the other in D1. When you select from the dropdown in B1 your selection loads the range name for the list used for the dropdown in D1. My test is B1 is three States and D1 is the Cities in that selected State. Hope this helps? JSW

P.S. The Private Sub automatically checks for a change in B1,s value and runs the macro to update D1.