Dependent dropdown lists

[Plirl]

Hi guys,

I am trying to create a sort of flow chart using dependent dropdown lists.

I have managed to get 3 dropdowns working, but I am struggling with the 4th dropdown.

In this example:
A2=ProductList
B2=INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"List")
C2=INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup"),2,0)&"List")

I am trying to now add a fourth column now to determine the Region of the where the fruit came from e.g. Mac Apple from Portugal, however I am receiving an error when trying the below formula.
D2=INDIRECT(VLOOKUP(C2,INDIRECT(VLOOKUP(B2,INDIRECT(VLOOKUP(A2,ProductLookup,2,0)&"Lookup),2,0),2,0)&"Lookup")





Portugal and Italy have been set up as a lookup table with Maclist - called MacLookup



I am unsure as to why I am receiving this error, and would appreciate any help I could get with it.

Thank you

 

[jorismoerings]

Hi and welcome to MrExcel,

I don't fully understand what you're trying to do because the question is "I am trying to now add a fourth column now to determine the Region of the where the fruit came from"
But the formula looks at Cell C2 which is not fruit but a recipe.

Anyway, look at this and see if this helps. It's based upon the assumption the question you've asked is leading and not the formula with the problem.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Product Type	Product	Recipe	Region
2	Red Fruit	Macintosh Apple	Apple Crisp	Italy
3
4
5	ProductList
6	Red Fruit
7	Yellow & Orange Fruit
8	Green Fruit
9
10	RedFruit					Mac	Bing		MacCntry	BingCntry
11	Macintosh Apple	Mac				Apple Pie	Cherry Pie		Portugal	Spain
12	Bing Cherries	Bing				Apple Crisp	Cherry Tarts		Italy	Greece
13
14	YellowOrangeFruit					Banana	Meyer
15	Banana	Banana				Banana Loaf	Lemonade
16	Meyer Lemon	Meyer				Banana Muffins	Lemon Ice Cream
17
18	GreenFruit					KeyLime	Grapes
19	Key Lime	KeyLime				Key Lime Pie	Grape Jam
20	Grapes	Grapes				Margaritas	Grape Jelly
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
A2	List	=$A$6:$A$8
B2	List	=INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A2;" ";"");"&";""));0;1)
C2	List	=INDEX(INDIRECT(VLOOKUP(B2;INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A2;" ";"");"&";""));0;0);2;0));0)
D2	List	=INDEX(INDIRECT(VLOOKUP(B2;INDEX(INDIRECT(SUBSTITUTE(SUBSTITUTE(A2;" ";"");"&";""));0;0);2;0)&"Cntry");0)