Drop Down List and Sub-lists of Differing Sizes

[EasterGreen]

I want to use a drop down menu to select any 1 of x areas, let's say these are the States where we have sales people. That's ok with Data Validation/Lists.
Each sales area or State may have between 5 and 20 counties where we have customers.
How can I select a State and then have each county populate into a range below? I can then do lookups by county for a variety of data points.
See attached image for what I mean.
Grateful for any help.


Stretch goal!
The number of rows where the list of counties can populate can be fixed to a maximum so I can have a predictable location of a total row, but could it also be dynamic?
That is, the total will move up and down as the population above it changes.

 

[Jeffrey Mahoney]

I can help with this. I love dynamic named ranges and creating dependent drop down lists.

What version of Excel are you using? Office 365 has some really cool features, but we can still create formulas in older versions.

I think your tables are a little off. Don't you want to include all your unique Areas in one table? Then you need a table that has all your Areas with all the Counties and sales.

Is this your real data set?

Are you ready for a little back and forth and some mind stretching formulas??

 

[EasterGreen]

Great, thanks.
Yes, I have one data set with all Areas and Counties in it. I also have another data set with all sales transactions and these have Areas and Counties as data columns.
I want to select an Area, populate dynamically the Counties and sum sales by County.

 

[Jeffrey Mahoney]

What version of excel are you using?

Can you post the real tables?

 

[EasterGreen]

Office 365.
I can't upload the data. The area/county one is about 200 rows and the sales one is 5,000 rows by 50 columns.

 

[Jeffrey Mahoney]

Ok, create a unique list of your areas. How many rows are in that?

 

[Jeffrey Mahoney]

Then we need to create a comprehensive list with all of your areas all of your sub areas and all of your sales.

 

[Jeffrey Mahoney]

If you could post even just a portion of those tables then I could get started

 

[EasterGreen]

Area1	County1_01	Customer01	454​
Area1	County1_02	Customer02	600​
Area1	County1_03	Customer03	242​
Area1	County1_04	Customer04	382​
Area1	County1_05	Customer05	115​
Area1	County1_06	Customer06	827​
Area1	County1_07	Customer07	384​
Area1	County1_08	Customer08	41​
Area2	County2_01	Customer01	986​
Area3	County3_01	Customer02	393​
Area3	County3_02	Customer03	531​
Area3	County3_03	Customer04	218​
Area3	County3_04	Customer05	499​
Area3	County3_05	Customer06	245​
Area3	County3_06	Customer07	760​
Area3	County3_07	Customer08	863​
Area3	County3_08	Customer01	872​
Area3	County3_09	Customer02	168​
Area3	County3_10	Customer03	379​
Area3	County3_11	Customer04	696​
Area3	County3_12	Customer05	722​
Area3	County3_13	Customer06	857​
Area3	County3_14	Customer07	329​
Area3	County3_15	Customer08	34​
Area3	County3_16	Customer01	158​
Area3	County3_17	Customer02	621​
Area3	County3_18	Customer03	942​
Area3	County3_19	Customer04	279​
Area4	County4_01	Customer05	794​
Area4	County4_02	Customer06	602​
Area4	County4_03	Customer07	193​
Area4	County4_04	Customer08	737​
Area4	County4_05	Customer01	313​
Area4	County4_06	Customer02	958​

 

[EasterGreen]

Office 365 version of Excel.