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

 

[Lacan]

Dear Toadstool,

Here you can check the workbook: Fernando Ramos sent you 1 item

As you can see from every 31 references in "F" (299 Lx - Amadora (MÁgua - Sul; 300 Lx - Amadora (MÁgua - Norte);...) column ordered in an ascendent way want the corresponding match in L:O column: Centro, Localidade, Especialidade and Nome.

Thanks again.

 

[Toadstool]

Lacan,

This format is not the format for which I constructed the formulae. The data entry is quite different and now you have 5 entry fields (Brick, Centro, Localidade, Especialidade, Nome) and not the 4 originally.

I was building formulae for columns L to O when there were sets of 5 rows for the four columns of data selection, that's why I was dividing by 5 to try and figure out the last active cell so it would calculate which set of 5 was having data entered.
=CHOOSE(MIN(INT(ROW(INDIRECT($L$2))+4)/5,3),CELL($M$1,B3),CELL(M1,B8),CELL($M$1,B13))

Now I see another format in columns E to J with inconsistent rows between data groups so that method won't work. The CHOOSE statement is returning a #VALUE error because it only allows for a selection of 1 as you've removed the other options.
=CHOOSE(MIN(INT(ROW(INDIRECT($Z$2))+4)/5,3),CELL($AA$1,F18))

I would suggest that you finalize your format for the data entry and ensure there is consistency if you need multiple groups of data entry.

 

[Lacan]

Dear Toadstool,

Here is my last format: Fernando Ramos sent you 1 item

Data in Column "F" doesn´t need data validation list.

More information please let me know.

Hope we can finally finish this bold project in excel .

Thanks a lot.

 

[Lacan]

Dear Toadstool,

Need more information?

Thanks pal.

 

[Toadstool]

Lacan,

The sheet needed repair after which I changed the sheet to xlsx from macro enabled, removed names with invalid references, removed unused external references, standardised Brick to be five columns wide starting in column E and removed the CHOOSE function.

The rows between Brick are not standard so I'm changing to a range search to find the Brick dependant on the last active CELL. This means, for example, when completing an entry it will base Centro on the Brick in E18 if the last active cell was between row 18 and 27.

The CENTRO LOCALIDADE ESPECIALIDADE and NOME must be completed in the same row from left to right so that the next column to be completed knows which previous value to use for generation of the distinct options for data validation.

This will take another few hours to complete and test so with other work I've underway it may take a couple of days to get back to you.

 

[Lacan]

Excellent Toadstool.

I´m very curious to see your expertise putting everything working together.

As this Excel Topic never been assured in this specific form hope this thread contribute even more for a better excel understanding and an exponential growth of our forum community.

Thank you very much.

 

[Toadstool]

Lacan,

I don't think others should copy this style as it's slow and kludgy. To satisfy your requirement with any speed and elegance would really need VBA.

Here it is, but as I previously said you need to do a Brick at a time and always select from the dropdowns left to right on the same row.

Lacan15.xlsx

...and don't forget the first thing you should do is go to AA1 and change Address to ENDEREÇO

 

[Lacan]

Excellent Toadstool.

Very excited to try it and to give positive feedback as always.

Thank you thank you very much.