#### willyrast

##### New Member
Hi everyone
I'm trying to make 2 pull down menus
The first on will display A, B and C
The second one will display something depending of the first one
If A or B is selected in the first one then the seconde one will display E, F and G but if C is selected in the first one than the second one will display only F and G

Hope that someone can help me with this problem

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

#### stanleydgromjr

##### Banned
Data Validation Lists dependent on other Lists

Data Validation Lists dependent on other Lists

With a solution:
rajibdas
Aug 31, 2006 9:19 am Post subject:

I have adjusted the solution from 'rajibdas':

In a test spreadsheet create the following:
Cell A1 contains a Data Validation List looking at column 'E', cells 'E2:E6':
Data Validation Lists dependent on other Lists.xls
ABCDEFGH
1AustraliaAustralia-City4CountryIndiaUSAustralia
2
3AustraliaIndia-City1US-City1Australia-City1
4IndiaIndia-City2US-City2Australia-City2
5USIndia-City3US-City3Australia-City3
6India-City4US-City4Australia-City4
7India-City5US-City5Australia-City5
8US-City6Australia-City6
9US-City7Australia-City7
10US-City8
11US-City9
Sheet1

Copy the following code:

'-------------------Start of Code---------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'
' The following code was modigied on 09/13/2006 by Stanley D. Grom, Jr.
' The original code was posted to Mr. Excel
' by rajibdas, Aug 31, 2006 9:19 am Post subject:

If Intersect(Target, Range("A1")) Is Nothing Then 'cell where you'll have the inital dropdown list
Exit Sub

ElseIf Range("A1").Value = "" Then ' if cell A1 blank, blank B1
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation ' create the second dropdown on cells B1
.Delete

End With
Range("A1").Select
Exit Sub

ElseIf Range("A1").Value = "India" Then ' if cell A1 is the name you select
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation ' create the second dropdown on cells B1
.Delete
xlBetween, Formula1:="=\$F\$2:\$F\$8" ' the drop down for states (change to where ever the entire list is)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub

ElseIf Range("A1").Value = "US" Then
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation
.Delete
xlBetween, Formula1:="=\$G\$2:\$G\$12"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub

ElseIf Range("A1").Value = "Australia" Then
Range("B1").Select
Range("B1").ClearContents
With Selection.Validation
.Delete
xlBetween, Formula1:="=\$H\$2:\$H\$10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Exit Sub
End If

End Sub
'-------------------End of Code---------------------

Press the 'ALT' and 'F11' keys, and paste in the 'Microsoft Excel Objects' Sheet1(Sheet1)'

I hope this helps.

Have a great day,
Stan

#### willyrast

##### New Member
Thanks man
But the next thing is that i have 50 pull down menu's ( A1:A51)
How do I make it so it will work for all the 50 the same way you showed now?

#### willyrast

##### New Member
I'm not a programer but with the help of Stan i solved one of the problems. The next problem is that instead of pull down memu's in cell A1 and B1 i've 50. In cell A1:A51 en so on. Can any one help me with this? I am now using the code Stan posted.

#### tusharm

##### MrExcel MVP
The only solution that makes any sense to me is one that can work with data in a relational database. One approach that does just that is at
While other approaches have intuitive appeal, they are not scalable and require *structural* changes whenever data values are added/deleted!

I'm not a programer but with the help of Stan i solved one of the problems. The next problem is that instead of pull down memu's in cell A1 and B1 i've 50. In cell A1:A51 en so on. Can any one help me with this? I am now using the code Stan posted.

Replies
1
Views
540
Replies
2
Views
887
Replies
3
Views
320
Replies
3
Views
265
Replies
3
Views
245

1,171,063
Messages
5,873,558
Members
432,984
Latest member
WilMel

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back