How to create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row? (Cascade Drop Down Menu)

[Lacan]

Hello Guys,

Through your help and many other tutorial excel videos came this way that almost Im achieving the goal - create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row.

Have this massive extent List with 7 columns and more than 2300 rows I will give you the sample data very developed to ask for your expertise feedback.

Without Vba and without filter and sort function (because unfortunately in my professional lap top dont have this 2 functions in excel version).

The Goals:

1. Make data validation with unique values from every List in every Columns (in The Main Table) to make more easier and simple to choose for instead of duplicates ones like this:

2. Create a Data Validation Drop-down list based on another Data Validation Drop-down list in precedent Row:

a) When I choose in The Main Table:

299 Lx - Amadora (MÁgua - Sul) > HOSPITAL JB LONDON > Amadora > (Logical should only get 2 options "Fernando" or "Maria") instead get this:

ALL DATA:

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	DATA LIST						THE MAIN TABLE
2	Brick	Center	City	Name			Brick	Center	City	Name
3	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Anna			299 Lx - Amadora (MÁgua - Sul)	HOSPITAL JB LONDON	Amadora	Dynom	2	HOSPITAL JB LONDON
4	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Anna Simon
5	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Aladin
6	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Porto	Serge
7	299 Lx - Amadora (MÁgua - Sul)	HOSPITAL JB LONDON	Amadora	Fernando
8	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Blyde
9	299 Lx - Amadora (MÁgua - Sul)	HOSPITAL JACK BARCELONA	Porto	Dynom
10	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Venom
11	300 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO FRANK	Amadora	Yalan
12	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Anna			0
13	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Costa
14	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Maria
15	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Cesar
16	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Carlos
17	300 Lx - Amadora (MÁgua - Sul)	HOSPITAL JB LONDON	Amadora	Maria
18	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR JONH	Amadora	Jesus
19	301 Lx - Amadora (MÁgua - Sul)	Clínica LAR JONH	Amadora	Maria
20	301 Lx - Amadora (MÁgua - Sul)	Clínica LAR JONH	Amadora	Luzia
21	301 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Guilherme
22	301 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Rogerio
23	301 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Pedro
24	301 Lx - Amadora (MÁgua - Sul)	DR PETER SA	Samouco	Albino
25	302 Lx	CLINIC ABC	Lisboa	Naxos
26
27
Esta (2)

Cell Formulas
Range	Formula
L3	L3	=SUM(COUNTIFS(C3:C25,I3, D3:D25,J3))
M3	M3	=INDEX(C3:E25, MATCH(I3,C3:C25,0), MATCH(J3,D3:D25,0))
H12	H12	=COUNTIF(INDIRECT(brick),H12)<2

Named Ranges
Name	Refers To	Cells
'Esta (2)'!_FilterDatabase	='Esta (2)'!$B$2:$E$25	L3:M3, H12
brick	='Esta (2)'!$B$3:$B$25	H12
'Esta (2)'!Lx_299	='Esta (2)'!$C$3:$E$10	L3:M3

Cells with Data Validation
Cell	Allow	Criteria
I4	List	=INDIRETO(H20)
I5:I10,J4:K10	List	=INDIRETO(SUBST(H5;" ";""))
H3	List	=brick
I3	List	=DESLOCAMENTO($C$2; CORRESP(H$3;$B$3:$B$25;0);0;CONTAR.SE($B$3:$B$25;$H$3);1)
J3	List	=DESLOCAMENTO($D$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($C$3:$C$25;$I$3);1)
K3	List	=DESLOCAMENTO($E$2; CORRESP(I$3;$C$3:$C$25;0);0;CONTAR.SE($D$3:$D$25;$J$3);1)

Can you help me guys?

Thank you very much.

 

[Toadstool]

Hi Lacan,

I believe I have done what you ask... but you may need to take coffee breaks between dropdown selections as it takes 5 seconds to respond on my PC.

Lacan.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1												$L$4:$L$6	$M$4:$M$5	$N$4:$N$5	$O$4:$O$5
2		Area	Centre	City	Name		Area	Centre	City	Name			$G$5	$H$5	$I$5
3		299 Gotham	Adams Clinic	Gotham	John		302 Xanadu	Holby General	Gotham	Michael		Area	Centre	City	Name
4		299 Gotham	Adams Clinic	Gotham	Sarah		301 Sherwood	Faith Medical	Rome	Gillian		299 Gotham	Adams Clinic	Gotham	Margaret
5		299 Gotham	Adams Clinic	Gotham	Simon		299 Gotham	Adams Clinic	Paris	James		301 Sherwood	Faith Medical	Paris	James
6		299 Gotham	Adams Clinic	Paris	Margaret							302 Xanadu
7		299 Gotham	Adams Clinic	Paris	James
8		299 Gotham	Faith Medical	Paris	Peter
9		299 Gotham	Faith Medical	Gotham	Vanessa
10		301 Sherwood	Faith Medical	Gotham	Susan
11		301 Sherwood	Faith Medical	Rome	Gillian
12		301 Sherwood	Holby General	Rome	Alex
13		301 Sherwood	Holby General	Rome	James
14		302 Xanadu	Holby General	Paris	Gary
15		302 Xanadu	Holby General	Gotham	Michael
16		302 Xanadu	Clinic Osso	Paris	Thomas
17		302 Xanadu	Clinic Osso	Milan	Thomas
18
Sheet1

Cell Formulas
Range	Formula
L1:O1	L1	=CELL("address",L4)&":"&ADDRESS(COUNTIF(L$4:L$1000,"> ")+ROW(L$3),COLUMN())
M2:O2	M2	=ADDRESS(COUNTIF(G$3:G$10,"> ")+ROW(G$2),COLUMN(G$2))
L4:L18	L4	=INDEX(B$3:B$2500,MATCH(0,INDEX(COUNTIF(L$3:L3,B$3:B$2500),),0))&""
M4:M18	M4	=IFERROR(INDEX(C$3:C$2500,AGGREGATE(15,6,ROW(C$3:C$2500)-ROW($C$2)/((B$3:B$2500=INDIRECT(M$2))*(COUNTIF(M$3:M3,C$3:C$2500)=0)),1)),"")
N4:N18	N4	=IFERROR(INDEX(D$3:D$2500,AGGREGATE(15,6,ROW(D$3:D$2500)-ROW($C$2)/((C$3:C$2500=INDIRECT(N$2))*(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(N$3:N3,D$3:D$2500)=0)),1)),"")
O4:O18	O4	=IFERROR(INDEX(E$3:E$2500,AGGREGATE(15,6,ROW(E$3:E$2500)-ROW($C$2)/((D$3:D$2500=INDIRECT(O$2))*(C$3:C$2500=INDIRECT(N$2))*(B$3:B$2500=INDIRECT(M$2))*(COUNTIF(O$3:O3,E$3:E$2500)=0)),1)),"")

Cells with Data Validation
Cell	Allow	Criteria
G3:J12	List	=INDIRECT(L$1)

 

[Fluff]

Duplicate How to make a Data Validation Dependent Drop Down List From the precedent Row?

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).