Dropdown box display only data dependent on another dropdown box?

[Chris]

I would like to create on dropdown box with the state and then a second drop
down box display only the counties for the state that was chosen in the first
dropdown box. Is that possible?

Thanks,
Chirs

 

[John C]

Since no data layout is given, I am assuming that you have lists of counties
for each state. I will also assume that the state dropdown box is the 2
letter code for the state.
State drop down box (cell A1)
Data|Validation, Allow: List, Source: =StateList
County drop down box (cell A2)
Data|Validation, Allow: List, Source: =INDIRECT("CountyList"&A1)

If this isn't what you are looking for, please provide more details as to
how your data is laid out.

 

[Chris]

John,

In column A I have each state listed for each county in column b. i.e.

A B
TX Dallas
TX Denton
TX Fannin

I can't seem to get the drop down box to display the state only once and i
also can't get the indirect statement to work. Thank you in advance for any
help you can provide.

Chris

 

[Pete_UK]

Have a look at Debra Dalgleish's site for further detailed instruction
on how to achieve this:

http://www.contextures.com/xlDataVal02.html

Hope this helps.

Pete

 

[John C]

Non-VBA solution....
Insert 2 columns before column A, so now your statelist is column C, and
your county list is column D.
A2: =IF(COUNTIF($C$2:$C2,C2)=1,MAX($A$1:A1)+1,"")
B2: =IF(C2=myState,MAX($B$1:B1)+1,"")
Copy A2 & B2 formulas down to bottom of data set.
E2: =IF(ROW()-1>MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE))
F2: =IF(ROW()-1>MAX($B:$B),"",VLOOKUP(ROW()-1,$B$2:$D$1000,3,FALSE))
I copied E2 down to E51, and F2 down to F101. Not sure if you have all 50
states, or if you are including territories, not sure if any state has more
than 100 counties, expand or modify the variables below as needed, and where
you need the formulas in E and F above as needed.

Then, in my example, I have named the cell that your drop down box for your
state is as myState.
I had to define two other variables, State List and County List
Insert|Name|Define:
StateList: =OFFSET(Sheet1!$E$2,0,0,50-COUNTBLANK(Sheet1!$E$2:$E$51),1)
CountyList: =OFFSET(Sheet1!$F$2,0,0,100-COUNTBLANK(Sheet1!$F$2:$F$101),1)
Using Data Validation for the drop down box for your state
Data|Validation, Allow: List, Source: =StateList
The drop down box for your count
Data|Validation, Allow: List, Source: =CountyList

 

[Chris]

John,

So far so good, but I get a name error message in column b and f. I know is
has to do with your formlua below referencing "myState". What exactly is
myState and how do I name/assign it to what I believe is the range you are
setting?

 

[John C]

Then, in my example, I have named the cell that your drop down box for your
state is as myState

Click on the cell that has the drop down box for your state list. Click on
the Name box to the left of the formula bar (should be displaying the current
cell), and then type in myState.
Or, if you prefer, assume the cell that your drop down box for states is
cell B2, you could replace all references to myState to $B$2.

 

[Gord Dibben]

See Debra Dalgleish's site for instructions using one cell only.

http://www.contextures.on.ca/xlDataVal02.html


Gord Dibben MS Excel MVP

 

[Chris]

John,

That did it! Thank you very much for your help. My spreadsheet is working
great!!!