0
votes

I created a drop-down list through data validation of the 50 states.

I want to select a state from that drop-down and populate an entire separate column full of that states counties. I have a table on a separate worksheet that has columns "State" and "County" as A and B respectively. column A has the state name the entire length of the counties in column B.

I have searched for about 2 hours through the internet and cannot seem to pinpoint one best way. Any help would be great

1
what you mean by "entire length of the counties in column B" ? all the counties are in the same cell for each state?Gowtham Shiva
@GowthamShiva as in if there are 10 rows in column B, the state name would be listed 10 times, once per row, in column A.tlaw the outlaw

1 Answers

1
votes

Column A contains the states. Column C and D contains the states and the counties respectively.

Cell F1 has data validation drop down for the range $A$1:$A$14. Enter the formula in column G and drag down, or enter the formula for the entire column,

=IFERROR(INDEX(INDIRECT("C"&MATCH($F$1,C:C,0)+ROW()-ROW($1:$1)&":D999"),MATCH($F$1,INDIRECT("C"&MATCH($F$1,C:C,0)+ROW()-ROW($1:$1)&":C999"),0),2),"")

enter image description here

If you have the county names in a separate sheet modify the formula accordingly. Hope this helps.