Autopopulate column after dropdown?

[sammipd]

I have a list of states and their counties. I'm looking for a method to choose a state from a dropdown and a column of their associated counties appears. Or, if the user types in the 2 letter state name and it auto populates the counties. I've played around with dropdowns and v/h lookups with no success. Can you help?

 

[Yongle]

One way

In sheet DropDown
By formula
cell A2 copied down to A255

=OFFSET(Counties!A2,0,MATCH(DropDown!$A$1,Counties!$1:$1,0)-1)

<colgroup><col></colgroup><tbody>
</tbody>

Formula need repeating in 254 rows (= number of states in Texas)

In sheet Counties
Create named range named NY , CA etc
In my example
NY is Range ("A2:A255) - ie RefersTo is =Counties!$A$2:$A$255
CA is Range("B2:B255)
Every empty cells in named ranges contain an apostrophe to prevent formula returning Zero

Excel 2016 (Windows) 32 bit

	A​	B​
1​	NY
2​	NY_County01	=OFFSET(Counties!A2,0,MATCH(DropDown!$A$1,Counties!$1:$1,0)-1)
3​	NY_County02
4​	NY_County03
5​	NY_County04
6​	NY_County05
7​	NY_County06
8​	NY_County07
9​	NY_County08
10​	NY_County09
11​	NY_County10
12​	NY_County11
13​	NY_County12
14​	NY_County13
15​	NY_County14
16​	NY_County15
17​	NY_County16
18​	NY_County17
19​	NY_County18
20​	NY_County19
21​	NY_County20
22​	NY_County21

Sheet: DropDown

Excel 2016 (Windows) 32 bit

	A​	B​
1​	CA
2​	CA_County01	=OFFSET(Counties!A2,0,MATCH(DropDown!$A$1,Counties!$1:$1,0)-1)
3​	CA_County02
4​	CA_County03
5​	CA_County04
6​	CA_County05
7​	CA_County06
8​	CA_County07
9​	CA_County08
10​	CA_County09
11​	CA_County10
12​	CA_County11
13​	CA_County12
14​
15​
16​
17​
18​
19​
20​
21​
22​

Sheet: DropDown

Excel 2016 (Windows) 32 bit

	A​	B​	C​
1​	NY	CA
2​	NY_County01	CA_County01
3​	NY_County02	CA_County02
4​	NY_County03	CA_County03
5​	NY_County04	CA_County04
6​	NY_County05	CA_County05
7​	NY_County06	CA_County06
8​	NY_County07	CA_County07
9​	NY_County08	CA_County08
10​	NY_County09	CA_County09
11​	NY_County10	CA_County10
12​	NY_County11	CA_County11
13​	NY_County12	CA_County12
14​	NY_County13
15​	NY_County14
16​	NY_County15
17​	NY_County16
18​	NY_County17
19​	NY_County18
20​	NY_County19
21​	NY_County20
22​	NY_County21
23​	NY_County22
24​	NY_County23
25​	NY_County24
26​	NY_County25
27​	NY_County26
28​	NY_County27
29​	NY_County28
30​	NY_County29
31​	NY_County30
32​			all cells contain apostrophe or county name

Sheet: Counties

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/autopopulate-column-after-dropdown.41448/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[sammipd]

Thank you!

 

[Yongle]

Just spotted something - I created the named ranges to test something else on another thread!
- my formula is not using them!
- so you can ignore that bit
- simply put the counties in columns with the value that matches the drop-down as header in each column. That is what match is using to get the correct offset.

 

[sammipd]

My apologies! I was so focused on finding a solution I didn't even think about cross-posting. I will remember in the future.