Conditional Drop Down List Help, VLOOKUP and INDIRECT

[ramseysearcy]

[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]DOORSTYLE[/TD]
[TD][/TD]
[TD][/TD]
[TD]LANDEN[/TD]
[TD]AVALON[/TD]
[TD]SAYBROOKE[/TD]
[TD]OAKLAND[/TD]
[/TR]
[TR]
[TD]LANDEN[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AVALON[/TD]
[TD][/TD]
[TD][/TD]
[TD]MAPLE[/TD]
[TD]MAPLE[/TD]
[TD]BIRCH[/TD]
[TD]OAK[/TD]
[/TR]
[TR]
[TD]SAYBROOKE[/TD]
[TD][/TD]
[TD][/TD]
[TD]OAK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OAKLAND[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WESTBURY[/TD]
[TD][/TD]
[TD][/TD]
[TD]ANTIQUE PAINT[/TD]
[TD]AUTUMN[/TD]
[TD]AUTUMN[/TD]
[TD]NATURAL[/TD]
[/TR]
[TR]
[TD]SINCLAIR[/TD]
[TD][/TD]
[TD][/TD]
[TD]AUTUMN[/TD]
[TD]CAFÉ[/TD]
[TD]CAFÉ[/TD]
[TD]SADDLE[/TD]
[/TR]
[TR]
[TD]PIONEER[/TD]
[TD][/TD]
[TD][/TD]
[TD]CAFÉ[/TD]
[TD]FAWN[/TD]
[TD]FAWN[/TD]
[TD]SARSAPARILLA[/TD]
[/TR]
[TR]
[TD]GRAYSON[/TD]
[TD][/TD]
[TD][/TD]
[TD]FAWN[/TD]
[TD]JAVA GLAZE[/TD]
[TD]JAVA GLAZE[/TD]
[TD]UMBER[/TD]
[/TR]
[TR]
[TD]RADFORD[/TD]
[TD][/TD]
[TD][/TD]
[TD]FLAGSTONE[/TD]
[TD]NATURAL[/TD]
[TD]NATURAL[/TD]
[TD]WHEAT[/TD]
[/TR]
[TR]
[TD]KORBETT[/TD]
[TD][/TD]
[TD][/TD]
[TD]JAVA GLAZE[/TD]
[TD]PUM. GLAZE[/TD]
[TD]PUM. GLAZE[/TD]
[TD]STANDARD[/TD]
[/TR]
[TR]
[TD]WINSTEAD[/TD]
[TD][/TD]
[TD][/TD]
[TD]NATURAL[/TD]
[TD]ROUGE[/TD]
[TD]ROUGE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TEAGAN[/TD]
[TD][/TD]
[TD][/TD]
[TD]PUM. GLAZE[/TD]
[TD]SADDLE[/TD]
[TD]SADDLE[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EASTLAND[/TD]
[TD][/TD]
[TD][/TD]
[TD]ROUGE[/TD]
[TD]SARSAPARILLA[/TD]
[TD="colspan: 2"]SARSAPARILLA[/TD]
[/TR]
[TR]
[TD]WENTWORTH[/TD]
[TD][/TD]
[TD][/TD]
[TD]SADDLE[/TD]
[TD]UMBER[/TD]
[TD]UMBER[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BRIARCLIFF[/TD]
[TD][/TD]
[TD][/TD]
[TD]SARSAPARILLA[/TD]
[TD]STANDARD[/TD]
[TD]STANDARD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AYDEN[/TD]
[TD][/TD]
[TD][/TD]
[TD]UMBER[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DRYDEN[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]WHITE PAINT[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]HARRISON[/TD]
[TD][/TD]
[TD][/TD]
[TD]PAINT[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BENTON[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]STANDARD[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DURHAM[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]OVERTON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AUGUSTA[/TD]
[TD][/TD]
[TD][/TD]
[TD]5-PIECE[/TD]
[TD]SLAB[/TD]
[TD]5-PIECE[/TD]
[TD]SLAB[/TD]
[/TR]
[TR]
[TD]NANTUCKET[/TD]
[TD][/TD]
[TD][/TD]
[TD]SLAB[/TD]
[TD][/TD]
[TD]SLAB[/TD]
[/TR]
</tbody>[/TABLE]

Forgive me I have been doing my research on this and I am just not grasping if there is a way to do what I want with so many different variables. I have tried =INDIRECT but I run into an issue with the names. For example B1:B27 is my Door Styles. I made a list of them and named them DoorStyles, and then put them across the top as reference. From there I labeled D2:D4 as LandenWoods because they are the woods for Landen Door style. The next long list that starts Antique and ends Standard is the Color and I named it LandenColors, they are the colors Landen is available in. Then finally at bottom there is 5-Piece and Slab, I named them LandenDetails. Ok so when I start making what I want it looks like this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Door Style[/TD]
[TD]Wood[/TD]
[TD]Color[/TD]
[TD]Details[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I need drop downs for each space under those headers. I run into error issues because of the fact there is multiple colors that can use the same type of wood but not the same doorstyle. I know this is asking a lot but can anyone please help. I am using excel 2010, thanks in advance.

 

[ramseysearcy]

bump

 

[AlanY]

bump

try this,

data validation for
B27 =Sheet2!$B$2:$B$3
C27 =Sheet2!$C$2:$C$16
D27 =Sheet2!$D$2:$D$3

Excel 2012
	A	B	C	D	E	F	G	H	I	J
1	DOORSTYLE			LANDEN	AVALON	SAYBROOKE	OAKLAND	WESTBURY	SINCLAIR	PIONEER
2	LANDEN
3	AVALON			MAPLE	MAPLE	BIRCH	OAK
4	SAYBROOKE			OAK
5	OAKLAND
6	WESTBURY			ANTIQUE PAINT	AUTUMN	AUTUMN	NATURAL
7	SINCLAIR			AUTUMN	CAF	CAF	SADDLE
8	PIONEER			CAF	FAWN	FAWN	SARSAPARILLA
9	GRAYSON			FAWN	JAVA GLAZE	JAVA GLAZE	UMBER
10	RADFORD			FLAGSTONE	NATURAL	NATURAL	WHEAT
11	KORBETT			JAVA GLAZE	PUM. GLAZE	PUM. GLAZE	STANDARD
12	WINSTEAD			NATURAL	ROUGE	ROUGE
13	TEAGAN			PUM. GLAZE	SADDLE	SADDLE
14	EASTLAND			ROUGE	SARSAPARILLA	SARSAPARILLA
15	WENTWORTH			SADDLE	UMBER	UMBER
16	BRIARCLIFF			SARSAPARILLA	STANDARD	STANDARD
17	AYDEN			UMBER
18	DRYDEN			WHITE PAINT
19	HARRISON			PAINT
20	BENTON			STANDARD
21	DURHAM
22	OVERTON
23	AUGUSTA			5-PIECE	SLAB	5-PIECE	SLAB
24	NANTUCKET			SLAB		SLAB
25
26	Door Style	Wood	Color	Details
27	LANDEN	OAK	SARSAPARILLA	5-PIECE
28
Sheet1

sheet 2 is with all the options that can be hidden

Excel 2012
	A	B	C	D
1	Door Style	Wood	Color	Details
2		MAPLE	ANTIQUE PAINT	5-PIECE
3		OAK	AUTUMN	SLAB
4			CAF
5			FAWN
6			FLAGSTONE
7			JAVA GLAZE
8			NATURAL
9			PUM. GLAZE
10			ROUGE
11			SADDLE
12			SARSAPARILLA
13			UMBER
14			WHITE PAINT
15			PAINT
16			STANDARD
Sheet2

Cell Formulas
Range	Formula
B2	{=IF(IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$3:$D$4),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),"")=0,"",IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$3:$D$4),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),""))}
C2	{=IF(IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$6:$D$20),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),"")=0,"",IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$6:$D$20),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),""))}
D2	{=IF(IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$23:$D$24),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),"")=0,"",IFERROR(INDEX(Sheet1!$D$1:$G$24,SMALL(ROW(Sheet1!$D$23:$D$24),ROW(Sheet1!D1)),MATCH(Sheet1!$A$27,Sheet1!$D$1:$Z$1,0)),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.