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

[Lacan]

Hello Guys,

Have this massive extent List that would like to make a Data Validation Dependent Drop Down List from the precedent row.

Will give you a sample data with 4 examples to be very clear that what is the goal in order to choose from Data Validation Dependent Drop Down List from the precedent row.

Can you help me?

Thanks a lot.

Book1
	B	C	D	E	F	G	H	I	J	K
2	Brick	Center	City	Name			Brick	Center	City	Name
3	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Anna		Example 1	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Anna
4	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Anna Simon		Example 2	300 Lx - Amadora (MÁgua - Sul)	Clínica LAR MEDICO	Samouco	Anna
5	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Aladin		Example 3	301 Lx - Amadora (MÁgua - Sul)	Clínica LAR JONH	Amadora	Luzia
6	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Serge		Example 4	302 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Pedro
7	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Fernando
8	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Blyde
9	299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA	Amadora	Dynom
10	300 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
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	301 Lx - Amadora (MÁgua - Sul)	Hospital	Amadora	Maria
18	301 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	302 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Rogerio
23	302 Lx - Amadora (MÁgua - Sul)	Clínica MEDICA SA	Lisboa	Pedro
24	302 Lx - Amadora (MÁgua - Sul)	Dr Peter Sa	Samouco	Albino
25	302 Lx - Amadora (MÁgua - Sul)	Consultorio Dr King	Lisboa	Fernando
Folha6

 

[Toadstool]

You copied just those G12 to K15 to another sheet? Why?

This won't work if you do the input on another sheet as all the INDIRECT statements are pointing to the wrong place.

 

[Lacan]

Have this massive List - just want to have a sheet with only data and another sheet to make data validation drop down menu List.

Do you think it is possible and it is too much effort?

Thanks.

 

[Toadstool]

I'm surprised you're still pursuing this, given the recalculation time, but...

You could move data to a Data tab like this:

Lacan4.xlsx
	B	C	D	E
2	Area	Centre	City	Name
3	299 Gotham	Adams Clinic	Gotham	John
4	299 Gotham	Adams Clinic	Gotham	Sarah
5	299 Gotham	Adams Clinic	Gotham	Simon
6	299 Gotham	Adams Clinic	Paris	Margaret
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
Data

Then have a DVDropdown tab for the built list and data entry from dropdowns:

Lacan4.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1							$H$4:$H$6	$I$4:$I$5	$J$4:$J$4	$K$4:$K$5	Active	Address
2	Area		Centre	City	Name			$B$8	$D$8	$E$8	$D$8	$B$8
3	301 Sherwood		Faith Medical	City	Sarah		Area	Centre	City	Name
4			Faith Medical	Paris	Peter		299 Gotham	Faith Medical	Rome	Alex
5			Adams Clinic	Paris	Margaret		301 Sherwood	Holby General		James
6							302 Xanadu
7	Area		Centre	City	Name
8	301 Sherwood		Holby General	Rome	James
9			Faith Medical	Gotham	Susan
10
11
12	Area		Centre	City	Name
13	302 Xanadu		Faith Medical	Paris	Thomas
14
15
16
17	Area		Centre	City	Name
18	299 Gotham		Holby General	Paris	Gary
19
20
21
DVDropdown

Cell Formulas
Range	Formula
H1:K1	H1	=CELL($M$1,H4)&":"&ADDRESS(COUNTIF(H$4:H$1000,"> ")+ROW(H$3),COLUMN())
I2	I2	=M2
J2:K2	J2	=ADDRESS(ROW(INDIRECT($L$2)),COLUMN(D$1))
L2	L2	=CELL($M$1)
M2	M2	=CHOOSE(MIN(INT(ROW(INDIRECT($L$2))+4)/5,3),CELL($M$1,B3),CELL(M1,B8),CELL($M$1,B13))
H4:H21	H4	=INDEX(Data!B$3:B$2500,MATCH(0,INDEX(COUNTIF(H$3:H3,Data!B$3:B$2500),),0))&""
I4:I21	I4	=IFERROR(INDEX(Data!C$3:C$2500,AGGREGATE(15,6,ROW(Data!C$3:C$2500)-ROW(Data!$C$2)/((Data!B$3:B$2500=INDIRECT(I$2))*(COUNTIF(I$3:I3,Data!C$3:C$2500)=0)),1)),"")
J4:J21	J4	=IFERROR(INDEX(Data!D$3:D$2500,AGGREGATE(15,6,ROW(Data!D$3:D$2500)-ROW(Data!$C$2)/((Data!C$3:C$2500=INDIRECT(J$2))*(Data!B$3:B$2500=INDIRECT(I$2))*(COUNTIF(J$3:J3,Data!D$3:D$2500)=0)),1)),"")
K4:K21	K4	=IFERROR(INDEX(Data!E$3:E$2500,AGGREGATE(15,6,ROW(Data!E$3:E$2500)-ROW(Data!$C$2)/((Data!D$3:D$2500=INDIRECT(K$2))*(Data!C$3:C$2500=INDIRECT(J$2))*(Data!B$3:B$2500=INDIRECT(I$2))*(COUNTIF(K$3:K3,Data!E$3:E$2500)=0)),1)),"")

Cells with Data Validation
Cell	Allow	Criteria
D3:D5,D8:D10,D13:D15,D18:D20	List	=INDIRECT($I$1)
E3:E5,E8:E10,E13:E15,E18:E20	List	=INDIRECT($J$1)
F3:F5,F8:F10,F13:F15,F18:F20	List	=INDIRECT($K$1)
M1	List	endereço,Address
B3,B8,B13,B18	List	=INDIRECT($H$1)

 

[Lacan]

Dear Toadstool,

Yes almost finishing it.

If close this project wouldn´t believe also.

As you said just put data in other sheet and create a DVDropdown tab

Think have everything ok however "Localidade" dropdown doesn´t work.

What is wrong in this formulas?

Thanks again Toadstool.

Book1
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1																	$U$4:$U$34	$V$4:$V$10	$W$4:$W$3	$X$4:$X$3	$Y$4:$Y$3	Active	ENDEREÇO
2	JAN																	$F$18	$L$2	$M$2	$N$2	$V$2	$F$18
3	Vendas PVP			Evol %													Brick	Centro	Localidade	Especialidade	Nome
4	Alter	Mercado	MS	ALTER	Mercado	Ganho											299 Lx - Amadora (MÁgua - Sul)	CENTRO CLINICO AVENIDA
5	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A											300 Lx - Amadora (MÁgua - Norte)	CENTRO CLINICO SAO CRISTOVAO
6	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A											301 Lx - Amadora (Venteira)	Clínica LAR MEDICO / AMADORA
7	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A											302 Lx - Amadora (Alfragide, ÁLivres)	Clínica MEDICA OLIVENCA LDA
8																	303 Lx - Amadora (Falagueira, VNova)	Clínica POLICLINICA SANTA TERESA
9	MÊS																304 Lx - Amadora (ESol)	Consultorio DR. ALBINO ANTUNES
10	JAN																308 Lx - Lisboa (Benfica - Sul)	Consultorio DR. FERNANDO COSTA
11																	309 Lx - Lisboa (Benfica - Norte)
12																	310 Lx - Lisboa (Estrela)
13																	311 Lx - Lisboa (COurique - Oeste)
14																	312 Lx - Lisboa (COurique - Este)
15																	313 Lx - Lisboa (Misericórdia)
16																	314 Lx - Lisboa (SAntónio - Oeste)
17																	315 Lx - Lisboa (SAntónio - Este)
18		299 Lx - Amadora (MÁgua - Sul)						CENTRO	LOCALIDADE	ESPECIALIDADE	NOME	N.º	SEL	CAT			316 Lx - Lisboa (SMMaior)
19								CENTRO CLINICO SAO CRISTOVAO									320 Lx - Lisboa (SVicente)
20																	321 Lx - Lisboa (Campolide)
21	NOME		PRODUTOS	UNS	VALOR	OBSERVAÇÃO											331 Lx - Lisboa (SDBenfica - Sul)
22																	332 Lx - Lisboa (SDBenfica - Norte)
23																	333 Lx - Lisboa (Carnide)
24																	349 St - Seixal (Seixal, APPires, FFerro)
25																	350 St - Seixal (Arrentela)
26																	351 St - Seixal (Amora)
27																	352 St - Seixal (Corroios)
28																	353 St - Almada (Laranjeiro)
29		300 Lx - Amadora (MÁgua - Norte)															354 St - Almada (CPiedade)
30																	355 St - Almada (Almada, Cacilhas, Pragal)
31																	356 St - Almada (Feijó)
32	NOME		PRODUTOS	UNS	VALOR	OBSERVAÇÃO											357 St - Almada (Caparica, Trafaria)
33																	358 St - Almada (CCaparica)
34																	359 St - Almada (ChCaparica, Sobreda)
35
36
37
38
ANÁLISE

Cell Formulas
Range	Formula
U1:Y1	U1	=CELL($AA$1,U4)&":"&ADDRESS(COUNTIF(U$4:U$1000,"> ")+ROW(U$3),COLUMN())
E2	E2	=E10
V2	V2	=AA2
W2:Y2	W2	=ADDRESS(ROW(INDIRECT(($Z$2))),COLUMN(L$17))
Z2	Z2	=CELL($AA$1)
AA2	AA2	=CHOOSE(MIN(INT(ROW(INDIRECT($Z$2))+4)/5,3),CELL($AA$1,F18))
U4:U34	U4	=INDEX(Tabela3[Brick],MATCH(0,INDEX(COUNTIF(U$3:U3,Tabela3[Brick]),),0))&""
V4:V34	V4	=IFERROR(INDEX(Tabela3[Centro], AGGREGATE(15,6,ROW(Tabela3[Centro])-ROW(Tabela3[[#Headers],[Centro]])/((Tabela3[Brick]=INDIRECT(V$2))*(COUNTIF(V$3:V3,Tabela3[Centro])=0)),1)),"")
W4:W34	W4	=IFERROR(INDEX(Tabela3[Localidade],AGGREGATE(15,6,ROW(Tabela3[Localidade])-ROW(Tabela3[[#Headers],[Centro]])/((Tabela3[Centro]=INDIRECT(W$2))*(Tabela3[Brick]=INDIRECT(V$2))*(COUNTIF(W$3:W3,Tabela3[Localidade])=0)),1)),"")
X4:X34	X4	=IFERROR(INDEX(Tabela3[Especialidade],AGGREGATE(15,6,ROW(Tabela3[Especialidade])-ROW($C$2)/((Tabela3[Localidade]=INDIRECT(W$2))*(Tabela3[Centro]=INDIRECT(V$2))*(COUNTIF(X$3:X3,Tabela3[Especialidade])=0)),1)),"")
Y4:Y34	Y4	=IFERROR(INDEX(Tabela3[Nome],AGGREGATE(15,6,ROW(Tabela3[Nome])-ROW($C$2)/((Tabela3[Especialidade]=INDIRECT(X$2))*(Tabela3[Localidade]=INDIRECT(W$2))*(Tabela3[Centro]=INDIRECT(V$2))*(COUNTIF(Y$3:Y3,Tabela3[Nome])=0)),1)),"")
E5:E7	E5	=VLOOKUP(D5,INDIRECT($E$10),3,0)
F5	F5	=VLOOKUP(D6,INDIRECT($E$10),3,0)
G5:G7	G5	=VLOOKUP(D5,INDIRECT($E$10),4,0)
H5:H7	H5	=VLOOKUP(D5,INDIRECT($E$10),5,0)
I5:I7	I5	=VLOOKUP(D5,INDIRECT($E$10),6,0)
J5:J7	J5	=VLOOKUP(D5,INDIRECT($E$10),7,0)
F6:F7	F6	=VLOOKUP(D6,INDIRECT($E$10),3,0)
E18, E29	E18	=IFERROR(VLOOKUP(F18,MAT_NOV.18!$AA$7:$AB$37,2,0),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F29	Expression	=SE($J31<0;1)	text	NO
F18,F40,F53,F65,F76,F87,F98,F109,F121,F132,F143,F155,F166,F177,F193,F204,F215,F226,F237,F248,F259,F271,F283,F294,F305,F316,F328,F339,F350,F361	Expression	=SE($J20<0;1)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
L30:L38	List	=INDIRETO(AP$17)
L20:L27	List	=INDIRETO(AP$17)
F18:J18	List	=INDIRETO($U$1)
L19	List	=INDIRETO($V$1)
M19	List	=INDIRETO($W$1)
E10	List	=DADOS_TABELAS!$D$2:$D$13
I10	List	=TAB_ORIGINAL!$D$3:$D$229

 

[Toadstool]

Lacan,

I'm not seeing the names and tables for this new format sheet. If you want to dropbox me the workbook I'll take a look, otherwise I'm sorry but I can't assist further.

 

[Lacan]

Sure Toadstool.

How can I dropbox you the workbook?

Thanks again.

 

[Toadstool]

I'm not sure how to tell you so I'l let you figure out some kind of file-sharing operation.

 

[Lacan]

Deal.
PLease let me know.

Thanks pal.

 

[Lacan]

Just remember,

Can send through e-mail Toadstool?

Thanks again.

 

[Toadstool]

Lacan,
The forum administrators don't like events off-forum. You should be able to figure out how to use Dropbox or Google Drive to share your sheet.