Data Validation Lists

[Deelof]

I need to add drop down lists to select from into B2 based off the formula result in A2 (formula is in A3) and will return Colour or Size.
The lists in D & E columns will be on a different tab within my document and should form the drop down list based off the result in A2, i.e. A2 = Colour, drop down list is column D, A2 = Size, drop down list is column E.

 

[etaf]

in data validation put
=IF(A2="colour",D2:D4,IF(A2="Size",E2:E4,""))
or if a different sheet
=IF(A2="colour",Sheet2!D2:D4,IF(A2="Size",Sheet2!E2:E4,""))

Book9
	A	B	C	D	E
1				colour	size
2	size			red	1
3				green	2
4				pink	3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=IF(A2="colour",Sheet2!D2:D4,IF(A2="Size",Sheet2!E2:E4,""))

Book9
	A	B	C	D	E
1
2				orange	20
3				purple	25
4				yellow	35
Sheet2

as its on 2 sheets - i have added a share on dropbox

will only be there for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[Deelof]

Thank you. I am receiving an error for the data validation formula and believe this is due to the lists I have are not all singular words.
In my lists I have data such as:

Black Grain

Beige - Black

in data validation put
=IF(A2="colour",D2:D4,IF(A2="Size",E2:E4,""))
or if a different sheet
=IF(A2="colour",Sheet2!D2:D4,IF(A2="Size",Sheet2!E2:E4,""))

Book9
	A	B	C	D	E
1				colour	size
2	size			red	1
3				green	2
4				pink	3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	List	=IF(A2="colour",Sheet2!D2:D4,IF(A2="Size",Sheet2!E2:E4,""))

Book9
	A	B	C	D	E
1
2				orange	20
3				purple	25
4				yellow	35
Sheet2

as its on 2 sheets - i have added a share on dropbox

will only be there for a few days

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[etaf]

i have updated and added those words
multiple words are not a problem