Make a Drop Down List ( the challenge is to create the name based on matching range to make it dropable by the indirect formula )

[Lacan]

Hello Guys,

Have this List and would like to make a Drop Down List.

My challenge is to create a matching range base on the name of the Local cell like example "299 Lx - Amadora (MÁgua - Sul)" so ca use in indirect formula but excel gives an error everytime.

Any suggestions please?

Thanks guys

Copy Data:

Book1
	K	L	M	N	O	P
2	Local	Shop		Local		Shop
3	299 Lx - Amadora (MÁgua - Sul)	AB
4	299 Lx - Amadora (MÁgua - Sul)	BC
5	299 Lx - Amadora (MÁgua - Sul)	CD
6	299 Lx - Amadora (MÁgua - Sul)	DE
7	299 Lx - Amadora (MÁgua - Sul)	EF
8	300 Lx - Amadora (MÁgua - Norte)	AA
9	300 Lx - Amadora (MÁgua - Norte)	BB
10	300 Lx - Amadora (MÁgua - Norte)	CC
11	300 Lx - Amadora (MÁgua - Norte)	DD
12	300 Lx - Amadora (MÁgua - Norte)	EE
13	300 Lx - Amadora (MÁgua - Norte)	FF
14	301 Lx - Amadora (Venteira)	AE
15	301 Lx - Amadora (Venteira)	FA
16	301 Lx - Amadora (Venteira)	KA
17	301 Lx - Amadora (Venteira)	LO
18	301 Lx - Amadora (Venteira)	MI
Lista Médicos All

 

[DanteAmor]

A named range cannot start with a number, nor must it have spaces.
So I will put the named range for the shops for each Local as follows:

299 Lx - Amadora (MÁgua - Sul) : Lx_299
300 Lx - Amadora (MÁgua - Norte) : Lx_300
301 Lx - Amadora (Venteira) : Lx_301

Eg:

__________________________________________________________________________________
So these would be the formulas for the validation list:

varios 07feb2020.xlsm
	A	B	C	D	E	F
1	Local	Shop		299 Lx - Amadora (MÁgua - Sul)	300 Lx - Amadora (MÁgua - Norte)	301 Lx - Amadora (Venteira)
2	301 Lx - Amadora (Venteira)			AB	AA	AE
3				BC	BB	FA
4				CD	CC	KA
5				DE	DD	LO
6				EF	EE	MI
7					FF
DropDown

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$D$1:$F$1
B2	List	=INDIRECT(MID(A2,5,2)&"_"&LEFT(A2,3))

__________________________________________________________________________________
The above would look like this:

___________________________________________________________________________________
Attach my test file:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

__________________________________________________________________________________

 

[Peter_SSs]

Here is a way without worrying about named ranges or INDIRECT if you have the FILTER function (which I think you do have? Please update your 'Account details' so all members know what Excel & OS version(s) you are using)

The formula shown for P3 is entered in that cell only and the other results "spill" to any required cells below.
Data Validation is set up as 'List' as shown in column Q.

Lacan 2020-02-08.xlsm
	K	L	M	N	O	P	Q
2	Local	Shop		Local		Shop
3	299 Lx - Amadora (MÁgua - Sul)	AB		299 Lx - Amadora (MÁgua - Sul)		AB	CD
4	299 Lx - Amadora (MÁgua - Sul)	BC				BC
5	299 Lx - Amadora (MÁgua - Sul)	CD				CD
6	299 Lx - Amadora (MÁgua - Sul)	DE				DE
7	299 Lx - Amadora (MÁgua - Sul)	EF				EF
8	300 Lx - Amadora (MÁgua - Norte)	AA
9	300 Lx - Amadora (MÁgua - Norte)	BB
10	300 Lx - Amadora (MÁgua - Norte)	CC
11	300 Lx - Amadora (MÁgua - Norte)	DD
12	300 Lx - Amadora (MÁgua - Norte)	EE
13	300 Lx - Amadora (MÁgua - Norte)	FF
14	301 Lx - Amadora (Venteira)	AE
15	301 Lx - Amadora (Venteira)	FA
16	301 Lx - Amadora (Venteira)	KA
17	301 Lx - Amadora (Venteira)	LO
18	301 Lx - Amadora (Venteira)	MI
19
Sheet1

Cell Formulas
Range	Formula
P3:P7	P3	=FILTER(L3:L100,K3:K100=N3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
Q3:Q4	List	=$P$3#

 

[Lacan]

Here is a way without worrying about named ranges or INDIRECT if you have the FILTER function (which I think you do have? Please update your 'Account details' so all members know what Excel & OS version(s) you are using)

The formula shown for P3 is entered in that cell only and the other results "spill" to any required cells below.
Data Validation is set up as 'List' as shown in column Q.

Lacan 2020-02-08.xlsm
	K	L	M	N	O	P	Q
2	Local	Shop		Local		Shop
3	299 Lx - Amadora (MÁgua - Sul)	AB		299 Lx - Amadora (MÁgua - Sul)		AB	CD
4	299 Lx - Amadora (MÁgua - Sul)	BC				BC
5	299 Lx - Amadora (MÁgua - Sul)	CD				CD
6	299 Lx - Amadora (MÁgua - Sul)	DE				DE
7	299 Lx - Amadora (MÁgua - Sul)	EF				EF
8	300 Lx - Amadora (MÁgua - Norte)	AA
9	300 Lx - Amadora (MÁgua - Norte)	BB
10	300 Lx - Amadora (MÁgua - Norte)	CC
11	300 Lx - Amadora (MÁgua - Norte)	DD
12	300 Lx - Amadora (MÁgua - Norte)	EE
13	300 Lx - Amadora (MÁgua - Norte)	FF
14	301 Lx - Amadora (Venteira)	AE
15	301 Lx - Amadora (Venteira)	FA
16	301 Lx - Amadora (Venteira)	KA
17	301 Lx - Amadora (Venteira)	LO
18	301 Lx - Amadora (Venteira)	MI
19
Sheet1

Cell Formulas
Range	Formula
P3:P7	P3	=FILTER(L3:L100,K3:K100=N3)
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
Q3:Q4	List	=$P$3#

View attachment 6096

Works magic my friend.

However want to do a drop down dependendable list non repeated very simple in this case from "H2" reference cell:

Here is the data:

Book1
	C	D	E	F	G	H	I	J	K	L
2	Bio	Shop	Local			299 Lx - Amadora (MÁgua - Sul)			Shop	Local
3	299 Lx - Amadora (MÁgua - Sul)	AA	USA
4	300 Lx - Amadora (MÁgua - Norte)	BB	USA
5	301 Lx - Amadora (Venteira)	D	FRANCE
6	301 Lx - Amadora (Venteira)	EE	GERMAN
7	301 Lx - Amadora (Venteira)	F	PORTUGAL
8	301 Lx - Amadora (Venteira)	AA	PORTUGAL
9	301 Lx - Amadora (Venteira)	H	SPAIN
10	302 Lx - Amadora (Alfragide, ÁLivres)	J	SPAIN
11	302 Lx - Amadora (Alfragide, ÁLivres)	K	HUNGRY
12	302 Lx - Amadora (Alfragide, ÁLivres)	L	AFRICA
13	302 Lx - Amadora (Alfragide, ÁLivres)	G	AFRICA
14	303 Lx - Amadora (Falagueira, VNova)	GG	RUSSIA
15	304 Lx - Amadora (ESol)	G	RUSSIA
16	308 Lx - Lisboa (Benfica - Sul)	T	UK
Folha2

Thanks again.

 

[Peter_SSs]

Works magic my friend.

Good news! And thanks for updating your account.

.. want to do a drop down dependendable list non repeated very simple in this case from "H2" reference cell:

I'm not sure what you want. Can you explain in more detail and perhaps give an example of results as well?