dependant lists with an IF statement

[keithmct]

window		blind type		width		drop		brackets	dbl/link/ind	controls		bunch		blade size		Fabric		Colour
b1		PVCvenetian		1800		1500								63		Urbanwood		Alpine White
b2		PVCvenetian		1800		1500								50		Urbanwood		Linen

Hi all, I'm trying to limit the input of the colour column when the blade size is 63. Normal 50mm blade size has 9 options. The only 63mm options should be Alpine White or Warm White. I have put those colours in a new table called PVCvenetian63 and tried to amend formula in Data Validation, currently:
=INDIRECT(SUBSTITUTE(E14&S14," ","")) to:
=IF(Q14="63", PVCvenetian63, INDIRECT(SUBSTITUTE(E14&S14," ","")))
but this gives an error like "are you trying to type a formula?......). Any idea what is wrong?

 

[RobP]

would you be able to share your column references (for example, we can see Col Q must be "blade size", but we've no idea what your data is inside E14 and S14 ?

might help understand a little more whats going wrong.
thanks
Rob

 

[Georgiboy]

Looking at the below, I have named the two purple tables: PVCvenetian_50 & PVCvenetian_63
Data validation formula in cell U2 dragged down: =INDIRECT($E2&"_"&$Q2)

I also don't understand where column S (Fabric) comes into it?

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	window		blind type		width		drop		brackets	dbl/link/ind	controls		bunch		blade size		Fabric		Colour			50	63
2	b1		PVCvenetian		1800		1500								63		Urbanwood					Red	Alpine White
3	b2		PVCvenetian		1800		1500								50		Urbanwood					Green	Warm White
4																						Blue
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
U2:U3	List	=INDIRECT($E2&"_"&$Q2)

 

[keithmct]

Oops, sorry. E14 is Blind Type (PVCvenetian) and S14 is Fabric. Only PVC venetian fabrics appear when type is PVCvenetian and only, say, Urbanwood colours appear when fabric is Urbanwood.
So there are about 10 different blind types. Each different type has particular Fabrics and each fabric has particular colours.
Also for different blind types, there are other blade sizes (16, 25, 50, 63, 89, 100, 127mm)

 

[Georgiboy]

In that case you can have multiple lookup tables for thr blind types as below:

Tables named:

PVCvenetianCountrywood50​

PVCvenetianUrbanwood50​

PVCvenetianCountrywood63​

PVCvenetianUrbanwood63​

Book1
	C	D	E	F	G	H	I	J							Q	R	S	T	U	V	W	X	Y	Z	AA
1	window		blind type		width		drop								blade size		Fabric		Colour			PVCvenetianCountrywood50	PVCvenetianUrbanwood50	PVCvenetianCountrywood63	PVCvenetianUrbanwood63
2	b1		PVCvenetian		1800		1500								50		Countrywood					Red	White	Black	Orange
3	b2		PVCvenetian		1800		1500								50		Urbanwood					Blue	Cream	Grey	Pink
4	b3		PVCvenetian		1800		1500								63		Countrywood					Green	Yelow	Charcoal	Cyan
5	b4		PVCvenetian		1800		1500								63		Urbanwood
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
U2:U5	List	=INDIRECT($E2&$S2&$Q2)

 

[keithmct]

great solutions which would work, but are there any suggestions as to what might be wrong with my proposed formula?
=IF(Q14="63", PVCvenetian63, INDIRECT(SUBSTITUTE(E14&S14," ","")))

further info: I have (in this instance) a table called "PVCvenetian", a table called "Urbanwood" and for the colours a tables called "PVCvenetianurbanwood" which is what the substitute part is doing - removing the spaces so concatenating E14 & S14 point to the correct table for colours.