RE: Dropdown box display only data dependent on another dropdown b

Tech-Archive recommends: Fix windows errors by optimizing your registry

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


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

Quantcast