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]

There's a lot going on here. The Unique Area list is a dynamic range named Area_List. The Yellow table is an Excel Table and named ACCS_Tbl. The County Filter and the Customer Filter are also dynamic named ranges called County_list and Customer_List.

The formula for the Area_List is: =OFFSET(Sheet3!$B$1,1,0,MATCH("zzzzzzzzzzzzzz",Sheet3!$B:$B)-ROW(Sheet3!$B$1),1)
The formula for the County_List is: =OFFSET(Sheet3!$J$1,1,0,MATCH("zzzzzzzzzzzzz",Sheet3!$J:$J)-ROW(Sheet3!$J$1),1)
The Formula for the Customer List is: =OFFSET(Sheet3!$L$1,1,0,MATCH("zzzzzzzzzzzzzz",Sheet3!$L:$L)-ROW(Sheet3!$L$1),1)

I didn't know if you wanted to carry your dependent validations out to the customer names.

How this works is you first choose an Area from the AreaSelection. Then you choose a County, then a Customer. The sales field will sum the values based on those 3 criteria. In real time you can see the changes to the lists when you choose each selection.

Have fun.

Book2.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Unique Areas		Area	County	Customer	Sales			County Filter		Customer Filter			Area Selection	County Selection	Customer Selection	Sales
2	Area1		Area1	County1_01	Customer01	454			County3_01		Customer06			Area3	County3_05	Customer06	$ 245.00
3	Area2		Area1	County1_02	Customer02	600			County3_02
4	Area3		Area1	County1_03	Customer03	242			County3_03
5	Area4		Area1	County1_04	Customer04	382			County3_04
6			Area1	County1_05	Customer05	115			County3_05
7			Area1	County1_06	Customer06	827			County3_06
8			Area1	County1_07	Customer07	384			County3_07
9			Area1	County1_08	Customer08	41			County3_08
10			Area2	County2_01	Customer01	986			County3_09
11			Area3	County3_01	Customer02	393			County3_10
12			Area3	County3_02	Customer03	531			County3_11
13			Area3	County3_03	Customer04	218			County3_12
14			Area3	County3_04	Customer05	499			County3_13
15			Area3	County3_05	Customer06	245			County3_14
16			Area3	County3_06	Customer07	760			County3_15
17			Area3	County3_07	Customer08	863			County3_16
18			Area3	County3_08	Customer01	872			County3_17
19			Area3	County3_09	Customer02	168			County3_18
20			Area3	County3_10	Customer03	379			County3_19
21			Area3	County3_11	Customer04	696
22			Area3	County3_12	Customer05	722
23			Area3	County3_13	Customer06	857
24			Area3	County3_14	Customer07	329
25			Area3	County3_15	Customer08	34
26			Area3	County3_16	Customer01	158
27			Area3	County3_17	Customer02	621
28			Area3	County3_18	Customer03	942
29			Area3	County3_19	Customer04	279
30			Area4	County4_01	Customer05	794
31			Area4	County4_02	Customer06	602
32			Area4	County4_03	Customer07	193
33			Area4	County4_04	Customer08	737
34			Area4	County4_05	Customer01	313
35			Area4	County4_06	Customer02	958
Sheet3

Cell Formulas
Range	Formula
J2:J20	J2	=FILTER(ACCS_Tbl[County],ISNUMBER(SEARCH(AreaSelection,ACCS_Tbl[Area])),"Not Found")
L2	L2	=FILTER(ACCS_Tbl[Customer],ISNUMBER(SEARCH(CountySelection,ACCS_Tbl[County])),"Not Found")
R2	R2	=SUMIFS(ACCS_Tbl[Sales],ACCS_Tbl[Area],AreaSelection,ACCS_Tbl[County],CountySelection,ACCS_Tbl[Customer],CustomerSelection)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
AreaSelection	=Sheet3!$O$2	R2, J2
CountySelection	=Sheet3!$P$2	R2, L2
CustomerSelection	=Sheet3!$Q$2	R2

Cells with Data Validation
Cell	Allow	Criteria
O2	List	=Area_List
P2	List	=County_list
Q2	List	=Customer_List

 

[EasterGreen]

Wow, thanks. Hadn't really investigated OFFSET before.
Will give this a try and let you know.
I appreciate your time.

 

[Peter_SSs]

Perhaps I have not fully understood the requirement here but it seems to be way over-complicated to me.
It looks like for the moment we are assuming the dynamic array functions are in play (even though not all Excel 365 customers have them yet) and that we are trying to set up lists for Data validation of areas/counties/customers that are in the sales transactions table.

If we have the dynamic array functions, then there is no need for any named ranges, but in particular no need for the complication of setting up dynamic named ranges as the dynamic array functions have a built-in way to refer to there 'spill ranges' directly.

See if this would suffice (I have changed some of this ample data)

Formulas in columns I:K are entered in row 2 only and the other results automatically 'spill' to any required rows.
If you want, columns I:K could then be hidden.

Very simple Data Validation, without dynamic named ranges, is shown below.
I have included two options for obtaining the Sales figure.

EasterGreen 2020-05-16 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Area	County	Customer	Sales		Unique Areas	County Filter	Customer Filter		Area Selection	County Selection	Customer Selection	Sales	Sales
2		Area1	County1_01	Customer01	454		Area1	County3_01	Customer07		Area3	County3_06	Customer04	1553	1553
3		Area1	County1_02	Customer02	600		Area2	County3_02	Customer01
4		Area1	County1_03	Customer03	242		Area3	County3_03	Customer04
5		Area1	County1_04	Customer04	382		Area4	County3_04
6		Area1	County1_05	Customer05	115			County3_05
7		Area1	County1_06	Customer06	827			County3_06
8		Area1	County1_07	Customer07	384			County3_07
9		Area1	County1_08	Customer08	41			County3_08
10		Area2	County2_01	Customer01	986			County3_10
11		Area3	County3_01	Customer02	393			County3_09
12		Area3	County3_02	Customer03	531			County3_16
13		Area3	County3_03	Customer04	218			County3_17
14		Area3	County3_04	Customer05	499			County3_18
15		Area3	County3_05	Customer06	245			County3_19
16		Area3	County3_06	Customer07	760
17		Area3	County3_07	Customer08	863
18		Area3	County3_08	Customer03	872
19		Area3	County3_06	Customer01	168
20		Area3	County3_10	Customer05	379
21		Area3	County3_06	Customer04	696
22		Area3	County3_07	Customer08	722
23		Area3	County3_06	Customer04	857
24		Area3	County3_09	Customer02	329
25		Area3	County3_10	Customer03	34
26		Area3	County3_16	Customer01	158
27		Area3	County3_17	Customer02	621
28		Area3	County3_18	Customer03	942
29		Area3	County3_19	Customer04	279
30		Area4	County4_01	Customer05	794
31		Area4	County4_02	Customer06	602
32		Area4	County4_03	Customer07	193
33		Area4	County4_04	Customer08	737
34		Area4	County4_05	Customer01	313
35		Area4	County4_06	Customer02	958
36
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=UNIQUE(ACCS_tbl[Area])
J2:J15	J2	=UNIQUE(FILTER(ACCS_tbl[County],ACCS_tbl[Area]=M2,""))
K2:K4	K2	=UNIQUE(FILTER(ACCS_tbl[Customer],(ACCS_tbl[Area]=M2)*(ACCS_tbl[County]=N2)))
P2	P2	=SUMIFS(ACCS_tbl[Sales],ACCS_tbl[Area],M2,ACCS_tbl[County],N2,ACCS_tbl[Customer],O2)
Q2	Q2	=SUM(FILTER(ACCS_tbl[Sales],(ACCS_tbl[Area]=M2)*(ACCS_tbl[County]=N2)*(ACCS_tbl[Customer]=O2),0))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
M2	List	=$I$2#
N2	List	=$J$2#
O2	List	=$K$2#

 

[Jeffrey Mahoney]

I assumed that the data provided was not actual data and that there would be much more variety of values for each level.

 

[EasterGreen]

I'm afraid these are all too complicated for. I used the word dynamic but perhaps in the wrong context.
What I want to do is have a report created:
1. I select an Area from a drop down menu (I am able to create a list for data validation), say in cell A1
2. the name of this Area will then populate cells, say, A5..A(5+x) (x=see below)
3. the names of the Counties in this area will then populate cells B5..B(5+x) (where x is the number of Counties in that Area (between 1 and 20?)
4. I can then set up lookups or sums to bring in the revenue, shipping and margin totals from the data sheet
5. customer differentiation not that important right now as a secondary selection level.

i could just set out a report and leave 20 rows of a gap between header and total row but that may look untidy if only 1 or 2 Counties in that area.

Thanks anyway, been interesting exploring these formulae.