Dependent Drop Down or Other Advanced Solution

[IESEnergy]

I am having trouble with this dependent drop down. My Wattage sheet is my master table. My ADD sheet is where I want to add the information. I would like to be able to have only the items listed in the other categories be available in my drop down on the ADD sheet. For example, if I choose CFL Fixture then I only want the the three types of subcategories to show.

Even better would be where I could choose the lamp wattage in column E then only those type of fixtures would be available to choose in the Column B, C, and D and vice versa. Is this possible?

Here is a link to the file: http://s000.tinyupload.com/index.php?file_id=23805702498562756109

Master Table

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Order	Lighting Category	Lighting Subcategory	# of Lamps per Fixture	Lamp Wattage	Fixture Wattage	Lamp Type	FIXTURE Type	FIXTURE CODE	LAMP CODE	DESCRIPTION	BALLAST	LAMP / FIXT	WATT / LAMP	WATT / FIXT
15	14	CFL	CFL Fixture	6	26	162		Compact Fluorescent Light Fixtures	CF26/6-L	CF26W	Compact Fluorescent, (6) 26W lamp	Electronic	6	26	162
21	20	CFL	CFL Fixture	6	32	228		Compact Fluorescent Light Fixtures	CF32/6-L	CF32W	Compact Fluorescent, (6) 32W lamp	Electronic	6	32	228
29	28	CFL	CFL Fixture	6	42	282		Compact Fluorescent Light Fixtures	CF42/6-L	CF42W	Compact Fluorescent, (6) 42W lamp	Electronic	6	42	282
57	56	CFL	CFL Fixture	6	26	150		Compact Fluorescent Light Fixtures	CFQ26/6-L	CFQ26W	Compact Fluorescent, quad, (6) 26W lamp, BF=0.95	Electronic	6	26	150
82	81	CFL	CFL Fixture	6	32	186		Compact Fluorescent Light Fixtures	CFT32/6-L	CFM32W	Compact Fluorescent, twin or multi, (6) 32W lamp	Electronic	6	32	186
85	84	CFL	CFL Fixture	6	36	212		Compact Fluorescent Light Fixtures	CFT36/6-BX	CFT36W	Compact Fluorescent, Biax, (6) 36W lamp	Electronic	6	36	212
86	85	CFL	CFL Fixture	6	36	198		Compact Fluorescent Light Fixtures	CFT36/6-L	CFT36W	Compact Fluorescent, long Twin, (6) 36W lamp	Electronic	6	36	198
87	86	CFL	CFL Fixture	6	36	210		Compact Fluorescent Light Fixtures	CFT36/6-LH	CFT36W	Compact Fluorescent, long Twin, (6) 36W lamp/ High Ballast Factor	Electronic	6	36	210
104	103	CFL	CFL Fixture	6	40	204		Compact Fluorescent Light Fixtures	CFT40/6-BX	CFT40W	Compact Fluorescent, Biax, (6) 40W lamp	Electronic	6	40	204
105	104	CFL	CFL Fixture	6	40	220		Compact Fluorescent Light Fixtures	CFT40/6-L	CFT40W	Compact Fluorescent, long Twin, (6) 40W lamp	Electronic	6	40	220
106	105	CFL	CFL Fixture	6	40	233		Compact Fluorescent Light Fixtures	CFT40/6-LH	CFT40W	Compact Fluorescent, long Twin, (6) 40W lamp/ High Ballast Factor	Electronic	6	40	233
119	118	CFL	CFL Fixture	6	50	324		Compact Fluorescent Light Fixtures	CFT50/6-BX	CFT50W	Compact Fluorescent, Biax, (6) 50W lamp	Electronic	6	50	324
128	127	CFL	CFL Fixture	6	55	336		Compact Fluorescent Light Fixtures	CFT55/6-BX	CFT55W	Compact Fluorescent, Biax, (6) 55W lamp	Electronic	6	55	336
129	128	CFL	CFL Fixture	6	55	352		Compact Fluorescent Light Fixtures	CFT55/6-L	CFT55W	Compact Fluorescent, long Twin, (6) 55W lamp	Electronic	6	55	352
130	129	CFL	CFL Fixture	6	55	373		Compact Fluorescent Light Fixtures	CFT55/6-LH	CFT55W	Compact Fluorescent, long Twin, (6) 55W lamp/ High Ballast Factor	Electronic	6	55	373
259	265	Linear Fluorescent	Unknown	6	20	153		Linear Fluorescent Fixtures	F26SE	F20T12	Fluorescent, (6) 24", STD lamp	Mag-ES	6	20	153
260	266	Linear Fluorescent	Unknown	6	20	168		Linear Fluorescent Fixtures	F26SS	F20T12	Fluorescent, (6) 24", STD lamp	Mag-STD	6	20	168
327	333	Linear Fluorescent	Unknown	6	25	198		Linear Fluorescent Fixtures	F36EE	F30T12/ES	Fluorescent, (6) 36", ES lamp	Mag-ES	6	25	198
328	334	Linear Fluorescent	T8 Linear Fluorescent	6	25	134	T8	Linear Fluorescent Fixtures	F36ILL-R	F25T8	Fluorescent, (6) 36", T-8 lamp, Instant Start Ballast, RLO (BF<.85)	Electronic	6	25	134
329	335	Linear Fluorescent	Unknown	6	30	238		Linear Fluorescent Fixtures	F36SE	F30T12	Fluorescent, (6) 36", STD lamp	Mag-ES	6	30	238
331	514	Linear Fluorescent	T5HO Linear Fluorescent	6	54	351	T5	Linear Fluorescent Fixtures	F46GHL	F48T5/HO	Fluorescent, (6) 48", STD HO T5 lamp	Electronic	6	54	351
406	656	Linear Fluorescent	T5HO Linear Fluorescent	6	54	364	T5	Linear Fluorescent Fixtures	F46GHL-H	F54T5/HO	Fluorescent, (6) 48", STD HO T5 lamp HLO (BF: .96-1.2)	Electronic	6	54	364
501	507	Linear Fluorescent	Unknown	6	34	216		Linear Fluorescent Fixtures	F46EE	F40T12/ES	Fluorescent, (6) 48", ES lamp	Mag-ES	6	34	216
502	508	Linear Fluorescent	Unknown	6	34	186		Linear Fluorescent Fixtures	F46EL	F40T12/ES	Fluorescent, (6) 48", ES lamp	Electronic	6	34	186
503	509	Linear Fluorescent	Unknown	6	34	236		Linear Fluorescent Fixtures	F46ES	F40T12/ES	Fluorescent, (6) 48", ES lamp	Mag-STD	6	34	236
505	511	Linear Fluorescent	T8 Linear Fluorescent	6	32	175	T8	Linear Fluorescent Fixtures	F46ILL	F32T8	Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)	Electronic	6	32	175
506	512	Linear Fluorescent	T8 Linear Fluorescent	6	32	156	T8	Linear Fluorescent Fixtures	F46ILL-R	F32T8	Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, RLO (BF< .85)	Electronic	6	32	156
507	513	Linear Fluorescent	T8 Linear Fluorescent	6	32	182	T8	Linear Fluorescent Fixtures	F46LL	F32T8	Fluorescent, (6) 48", T-8 lamp, NLO (BF: .85-.95)	Electronic	6	32	182
509	515	Linear Fluorescent	Unknown	6	40	258		Linear Fluorescent Fixtures	F46SE	F40T12	Fluorescent, (6) 48", STD lamp	Mag-ES	6	40	258
510	516	Linear Fluorescent	Unknown	6	40	282		Linear Fluorescent Fixtures	F46SS	F40T12	Fluorescent, (6) 48", STD lamp	Mag-STD	6	40	282
554	510	Linear Fluorescent	Unknown HO	6	55	405		Linear Fluorescent Fixtures	F46EHS	F48T12/HO/ES	Fluorescent, (6) 48", ES HO lamp (3.5' lamp)	Mag-STD	6	55	405
635	644	Linear Fluorescent	T8 Linear Fluorescent	6	59	328	T8	Linear Fluorescent Fixtures	F86ILL	F96T8	Fluorescent, (6) 96", T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)	Electronic	6	59	328
636	645	Linear Fluorescent	Unknown	6	60	369		Linear Fluorescent Fixtures	F86EE	F96T12/ES	Fluorescent, (6) 96", ES lamp	Mag-ES	6	60	369
637	646	Linear Fluorescent	Unknown	6	60	330		Linear Fluorescent Fixtures	F86EL	F96T12/ES	Fluorescent, (6) 96", ES lamp	Electronic	6	60	330
639	648	Linear Fluorescent	T8 Linear Fluorescent	6	32	222	T8	Linear Fluorescent Fixtures	F46ILL-H	F32T8	Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, HLO (BF: .96-1.2)	Electronic	6	32	222
640	649	Linear Fluorescent	T8 Linear Fluorescent	6	32	255	T8	Linear Fluorescent Fixtures	F46ILL-V	F32T8	Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, VLO (BF: >1.2)	Electronic	6	32	255
641	650	Linear Fluorescent	T8 Linear Fluorescent	6	32	158	T8	Linear Fluorescent Fixtures	F46SIL	F30T8	Fluorescent, (6) 48", 30W T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)	Electronic	6	32	158
642	651	Linear Fluorescent	T8 Linear Fluorescent	6	32	144	T8	Linear Fluorescent Fixtures	F46SSIL	F28T8	Fluorescent, (6) 48", 28W T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)	Electronic	6	32	144
674	643	Linear Fluorescent	Unknown HO	6	95	721		Linear Fluorescent Fixtures	F86EHS	F96T12/HO/ES	Fluorescent, (6) 96", ES HO lamp	Mag-STD	6	95	721

<tbody>
</tbody>

Wattage​

Sheet to add the information

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
6	Lighting Category	Lighting Subcategory	# of Lamps per Fixture	Lamp Wattage	Fixture Wattage	Lamp Type	FIXTURE Type	FIXTURE CODE	LAMP CODE	DESCRIPTION	BALLAST	LAMP / FIXT	WATT / LAMP	WATT / FIXT
7	High Intensity Discharge

<tbody>
</tbody>

ADD​

Workbook Defined Names Name Refers To Type =OFFSET(CODES!$V:$V, 0, 0, COUNTA(CODES!$V:$V)) <tbody> </tbody>

<tbody>
</tbody>

	V
1	Lighting Category
2	CFL
3	Circular Fluorescent
4	Exit Sign
5	LED
6	Incandescent
7	Linear Fluorescent
8	High Intensity Discharge
9	Induction Fluorescent
10	U_Tube Fluorescent

<tbody>
</tbody>

CODES​

<tbody>
</tbody>

I tried making dependent drop downs the traditional way but I would need so many. For instance, for a metal halide fixture only these wattages are available.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Order	Lighting Category	Lighting Subcategory	# of Lamps per Fixture	Lamp Wattage	Fixture Wattage	Lamp Type	FIXTURE Type	FIXTURE CODE	LAMP CODE	DESCRIPTION	BALLAST	LAMP / FIXT	WATT / LAMP	WATT / FIXT
759	862	High Intensity Discharge	Metal Halide	1	32	43		Metal Halide Fixtures	MH32/1	MH32	Metal Halide, (1) 32W lamp	CWA	1	32	43
760	870	High Intensity Discharge	Metal Halide	1	35	44		Metal Halide Fixtures	MH35/1	MH35	Metal Halide, (1) 35W lamp	CWA	1	35	44
761	871	High Intensity Discharge	Metal Halide	1	50	72		Metal Halide Fixtures	MH50/1	MH50	Metal Halide, (1) 50W lamp	CWA	1	50	72
762	872	High Intensity Discharge	Metal Halide	1	70	95		Metal Halide Fixtures	MH70/1	MH70	Metal Halide, (1) 70W lamp	CWA	1	70	95
763	854	High Intensity Discharge	Metal Halide	1	100	128		Metal Halide Fixtures	MH100/1	MH100	Metal Halide, (1) 100W lamp	CWA	1	100	128
764	856	High Intensity Discharge	Metal Halide	1	150	190		Metal Halide Fixtures	MH150/1	MH150	Metal Halide, (1) 150W lamp	CWA	1	150	190
765	858	High Intensity Discharge	Metal Halide	1	175	215		Metal Halide Fixtures	MH175/1	MH175	Metal Halide, (1) 175W lamp	CWA	1	175	215
766	860	High Intensity Discharge	Metal Halide	1	200	232		Metal Halide Fixtures	MH200/1	MH200	Metal Halide, (1) 200W lamp	CWA	1	200	232
767	861	High Intensity Discharge	Metal Halide	1	250	295		Metal Halide Fixtures	MH250/1	MH250	Metal Halide, (1) 250W lamp	CWA	1	250	295
768	863	High Intensity Discharge	Metal Halide	1	300	342		Metal Halide Fixtures	MH300/1	MH300	Metal Halide, (1) 300W lamp	CWA	1	300	342
769	864	High Intensity Discharge	Metal Halide	1	320	365		Metal Halide Fixtures	MH320/1	MH320	Metal Halide, (1) 320W lamp	CWA	1	320	365
770	865	High Intensity Discharge	Metal Halide	1	350	400		Metal Halide Fixtures	MH350/1	MH350	Metal Halide, (1) 350W lamp	CWA	1	350	400
771	866	High Intensity Discharge	Metal Halide	1	360	430		Metal Halide Fixtures	MH360/1	MH360	Metal Halide, (1) 360W lamp	CWA	1	360	430
772	867	High Intensity Discharge	Metal Halide	1	400	458		Metal Halide Fixtures	MH400/1	MH400	Metal Halide, (1) 400W lamp	CWA	1	400	458
773	869	High Intensity Discharge	Metal Halide	1	450	508		Metal Halide Fixtures	MH450/1	MH450	Metal Halide, (1) 450W lamp	CWA	1	450	508
774	873	High Intensity Discharge	Metal Halide	1	750	850		Metal Halide Fixtures	MH750/1	MH750	Metal Halide, (1) 750W lamp	CWA	1	750	850
775	868	High Intensity Discharge	Metal Halide	2	400	916		Metal Halide Fixtures	MH400/2	MH400	Metal Halide, (2) 400W lamp	CWA	2	400	916
776	855	High Intensity Discharge	Metal Halide	1	1000	1080		Metal Halide Fixtures	MH1000/1	MH1000	Metal Halide, (1) 1000W lamp	CWA	1	1000	1080
777	857	High Intensity Discharge	Metal Halide	1	1500	1610		Metal Halide Fixtures	MH1500/1	MH1500	Metal Halide, (1) 1500W lamp	CWA	1	1500	1610
778	859	High Intensity Discharge	Metal Halide	1	1800	1875		Metal Halide Fixtures	MH1800/1	MH1800	Metal Halide, (1) 1800W lamp	CWA	1	1800	1875

<tbody>
</tbody>

Wattage​

This is how I was doing it before I gave up:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1	Lighting Category List		CFL List		Circular Fluorescent List		Delamp List		Exit Sign List		High Intensity Discharge List		Incandescent List		Induction Fluorescent List		LED List		Linear Fluorescent List		None List		U-Tube Fluorescent List		Lamps per Fixture List		_0		_1
2	CFL		CFL Fixture		Circular Fluorescent		Delamp		Exit Sign		High Pressure Sodium		Halogen Incandescent		Induction Fluorescent		LED Fixture		T5 Linear Fluorescent		DND		U-Tube Fluorescent		_0		0		_0.5
3	Circular Fluorescent		CFL Screw-In						Exit Sign LED		Mercury Vapor		Standard Incandescent				LED Screw-In		T5HO Linear Fluorescent		N/A				_1				_1.5
4	Delamp		CFL Screw-In LED								Metal Halide						LED Exit Sign		T8 Linear Fluorescent						_2				_2
5	Exit Sign		Cold Cathode CFL								Pulse Start Metal Halide						CFL Screw-In LED		T8HO Linear Fluorescent						_3				_3
6	High Intensity Discharge																		T10 Linear Fluorescent						_4				_4
7	Incandescent																		T12 Linear Fluorescent						_5				_5
8	Induction Fluorescent																		Unknown						_6				_6
9	LED																		Unknown HO						_8				_7
10	Linear Fluorescent																								_9				_7.5
11	U_Tube Fluorescent																								_10				_8
12	None																								_12				_9
13																													_10
14																													_10.5
15																													_11
16																													_12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Wattage Table Codes​

Is there an easier solution? Thank you in advance. I apologize if I did not post the pictures correctly.

 

[IESEnergy]

Hi can anyone help with this?