# HELP!!! Data Validation list problem???

#### kajettinc

##### New Member
I am creating a spreadsheet that has several dependent Data Validation dropdown lists. eg... Dropdown #1 has a list of states. The dependent dropdown has cities within the state.

I have listed all the states on a seperate data sheet and named the list. I also set up the same list of states horizontally on the data sheet with the city names below each state name. I named the lists for each set of cities with the state name. Since it is not allowed to have a space in the named list, an underscore was automatically inserted in place of the space when setting up the lists.

The dependent list of cities works fine as long as I dont select a state name with two words, like New York, New Mexico, etc.

Any suggestions as to what I am doing wrong would be great.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
The Validation list formulas aren't given, so I can't say is wrong with them. But this worked for me:
The validation list source for H1 is = ListOfStates
The validation list source for H2 is = CitiesInOneState

It uses the Named Ranges: (...'s for spacing)
. .Name................RefersTo

ListOfStates..........=Sheet1!\$A\$1:\$A\$3
AllCityData...........=Sheet1!\$C\$1:\$E\$6
StateChosen.........=INDEX(AllCityData, ,MATCH(Sheet1!\$H\$1, INDEX(AllCityData,1,), 0))
CitiesInOneState...=OFFSET(StateChosen,1,0,COUNTA(StateChosen)-1,1)
<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=85><b>A</b><td align=center width=85><b>B</b><td align=center width=85><b>C</b><td align=center width=85><b>D</b><td align=center width=85><b>E</b><td align=center width=85><b>F</b><td align=center width=85><b>G</b><td align=center width=85><b>H</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">New York</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">New York</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">N. California</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">West Virginia</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">select state></FONT><td bgcolor="#FFCC99" > <FONT color="#000000">N. California</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">N. California</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">New York City</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Mendocino</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Leesburg</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">select city></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">Guinda</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">West Virginia</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Brooklyn</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Davis</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Charleston</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Yonkers</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">San Francisco</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000">Guinda</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr></table>

I rearranged some things, and used your suggestions, and it seems to be working exactly the way I want it to!

Thanks for the help!!!

The only thing that would make it better is if the city cell would default to the first city in the list should the state cell be changed to a different state. In any case, what you suggested works great!

The only thing that would make it better is if the city cell would default to the first city in the list should the state cell be changed to a different state. In any case, what you suggested works great!

That requires Visual Basic.
Putting this routine in the sheet's code module will make that happen.
Code:
``````Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Static lastSelection As Range
Dim stateSelectionCell As Range
Dim citySelectionCell As Range
Dim validationList As Variant

If Not lastSelection Is Nothing Then

Set stateSelectionCell = Range("G1"): Rem adjust
Set citySelectionCell = Range("G2"): Rem adjust

validationList = Evaluate(ThisWorkbook.Names("CitiesInOneState").RefersTo)

With citySelectionCell
If Not IsNumeric(Application.Match(.Value, validationList, 0)) Then
Application.EnableEvents = False
.Value = validationList(1, 1)
Application.EnableEvents = True
End If
End With
End If

End If
Set lastSelection = Target ' *
End Sub``````
I was surprised the making a selection from a validation list didn't trigger a Change event. Perhaps that's specific to my Excel 2004. If possible, this should be in a Change event rather than SelectionChange.

Unfortunately, the users systems that will be running this sheet are restricted and the use of macros is prohibited. I don't think their systems will allow the XLS file to run if it utilizes Visual Basic. I doubt there is any way to utilize any of the non VB functions to trigger a Change event. I think I may have to just deal with it. I will, however try this on my copy, since my system will allow the VB coding. The end user wont be able to use it, though.

Thanks again!!!

<TABLE style="WIDTH: 509pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=677 border=0 x:str><COLGROUP><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1152" width=36><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" width=80><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2560" span=2 width=80><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 2720" width=85><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 2752" width=86><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2592" width=81><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2016" width=63><TBODY><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 27pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" width=36 height=14></TD><TD class=xl35 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80>Choice1</TD><TD class=xl35 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=86>Choice2</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 60pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=80></TD><TD class=xl42 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 64pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc00" width=85>New york</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc00" width=86>N california</TD><TD class=xl44 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ffcc00" width=81>West virginia</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 47pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=63></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl38 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffcc00">New york</TD><TD class=xl45 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99">Browklyn</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">New york city</TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">Mendocino</TD><TD class=xl43 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">Leesburg</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">Browklyn</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">Davis</TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99">Charleston</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99">Yonkers</TD><TD class=xl39 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext; BORDER-BOTTOM: #d4d0c8 0.5pt; BACKGROUND-COLOR: #ffff99">San Francisco</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl40 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #ffff99">Guinda</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl37 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=2>Named range</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Choice1</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent; mso-ignore: colspan" colSpan=3 x:str="'=Offset(\$F\$1,,,,CountA(\$F\$1:\$M\$1))">=Offset(\$F\$1,,,,CountA(\$F\$1:\$M\$1))</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Choice2</TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" x:str="'=\$F:\$F">=\$F:\$F</TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 10.5pt" height=14><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 10.5pt; BACKGROUND-COLOR: transparent" height=14></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl36 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD><TD class=xl41 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
First List In B2 :
-Data/Validation/List:
=Choice1
Second list in C2:
- Data/Validation/List:
=Offset(choice2,1,Match(B2;Choice1,0)-1,CountA(Offset(choice2,,Match(B2,Choice1,0)-1))-1)

First item of list:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "\$B\$2" And Target.Count = 1 Then
Target.Offset(0, 1) = Range("choice2")(1).Offset(1, Application.Match(Target, [choice1], 0) - 1)
End If
End Sub

http://boisgontierjacques.free.fr/fichiers/DonneesValidation/DropDown2levels.xls

JB

Last edited by a moderator:

Replies
8
Views
373
Replies
4
Views
385
Replies
2
Views
535
Replies
5
Views
126
Replies
6
Views
337

1,202,916
Messages
6,052,541
Members
444,591
Latest member
exceldummy774

### 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