Dropdown box display only data dependent on another dropdown box?

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

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.

--
John C


"Chris" wrote:

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,

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

"John C" wrote:

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.

--
John C


"Chris" wrote:

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

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

On Aug 5, 3:16*pm, Chris wrote:
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



"John C" wrote:
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.

--
John C

"Chris" wrote:

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- Hide quoted text -

- Show quoted text -

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()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE))
F2: =IF(ROW()-1MAX($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


--
John C


"Chris" wrote:

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

"John C" wrote:

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.

--
John C


"Chris" wrote:

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,

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" wrote:

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()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE))
F2: =IF(ROW()-1MAX($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


--
John C


"Chris" wrote:

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

"John C" wrote:

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.

--
John C


"Chris" wrote:

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

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.
--
John C


"Chris" wrote:

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" wrote:

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()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE))
F2: =IF(ROW()-1MAX($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


--
John C


"Chris" wrote:

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

"John C" wrote:

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.

--
John C


"Chris" wrote:

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

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

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


Gord Dibben MS Excel MVP

On Tue, 5 Aug 2008 06:45:01 -0700, Chris
wrote:

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,

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

"John C" wrote:

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.
--
John C


"Chris" wrote:

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" wrote:

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()-1MAX($A:$A),"",VLOOKUP(ROW()-1,$A$2:$C$1000,3,FALSE))
F2: =IF(ROW()-1MAX($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


--
John C


"Chris" wrote:

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

"John C" wrote:

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.

--
John C


"Chris" wrote:

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