Dependent Drop Down or Other Advanced Solution

IESEnergy

New Member
Joined
Aug 28, 2017
Messages
14
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

ABCDEFGHIJKLMNO
1OrderLighting CategoryLighting Subcategory# of Lamps per FixtureLamp WattageFixture WattageLamp TypeFIXTURE TypeFIXTURE CODELAMP CODEDESCRIPTIONBALLASTLAMP / FIXTWATT / LAMPWATT / FIXT
1514CFLCFL Fixture626162Compact Fluorescent Light FixturesCF26/6-LCF26WCompact Fluorescent, (6) 26W lampElectronic626162
2120CFLCFL Fixture632228Compact Fluorescent Light FixturesCF32/6-LCF32WCompact Fluorescent, (6) 32W lampElectronic632228
2928CFLCFL Fixture642282Compact Fluorescent Light FixturesCF42/6-LCF42WCompact Fluorescent, (6) 42W lampElectronic642282
5756CFLCFL Fixture626150Compact Fluorescent Light FixturesCFQ26/6-LCFQ26WCompact Fluorescent, quad, (6) 26W lamp, BF=0.95Electronic626150
8281CFLCFL Fixture632186Compact Fluorescent Light FixturesCFT32/6-LCFM32WCompact Fluorescent, twin or multi, (6) 32W lampElectronic632186
8584CFLCFL Fixture636212Compact Fluorescent Light FixturesCFT36/6-BXCFT36WCompact Fluorescent, Biax, (6) 36W lampElectronic636212
8685CFLCFL Fixture636198Compact Fluorescent Light FixturesCFT36/6-LCFT36WCompact Fluorescent, long Twin, (6) 36W lampElectronic636198
8786CFLCFL Fixture636210Compact Fluorescent Light FixturesCFT36/6-LHCFT36WCompact Fluorescent, long Twin, (6) 36W lamp/ High Ballast FactorElectronic636210
104103CFLCFL Fixture640204Compact Fluorescent Light FixturesCFT40/6-BXCFT40WCompact Fluorescent, Biax, (6) 40W lampElectronic640204
105104CFLCFL Fixture640220Compact Fluorescent Light FixturesCFT40/6-LCFT40WCompact Fluorescent, long Twin, (6) 40W lampElectronic640220
106105CFLCFL Fixture640233Compact Fluorescent Light FixturesCFT40/6-LHCFT40WCompact Fluorescent, long Twin, (6) 40W lamp/ High Ballast FactorElectronic640233
119118CFLCFL Fixture650324Compact Fluorescent Light FixturesCFT50/6-BXCFT50WCompact Fluorescent, Biax, (6) 50W lampElectronic650324
128127CFLCFL Fixture655336Compact Fluorescent Light FixturesCFT55/6-BXCFT55WCompact Fluorescent, Biax, (6) 55W lampElectronic655336
129128CFLCFL Fixture655352Compact Fluorescent Light FixturesCFT55/6-LCFT55WCompact Fluorescent, long Twin, (6) 55W lampElectronic655352
130129CFLCFL Fixture655373Compact Fluorescent Light FixturesCFT55/6-LHCFT55WCompact Fluorescent, long Twin, (6) 55W lamp/ High Ballast FactorElectronic655373
259265Linear FluorescentUnknown620153Linear Fluorescent FixturesF26SEF20T12Fluorescent, (6) 24", STD lampMag-ES620153
260266Linear FluorescentUnknown620168Linear Fluorescent FixturesF26SSF20T12Fluorescent, (6) 24", STD lampMag-STD620168
327333Linear FluorescentUnknown625198Linear Fluorescent FixturesF36EEF30T12/ESFluorescent, (6) 36", ES lampMag-ES625198
328334Linear FluorescentT8 Linear Fluorescent625134T8Linear Fluorescent FixturesF36ILL-RF25T8Fluorescent, (6) 36", T-8 lamp, Instant Start Ballast, RLO (BF<.85)Electronic625134
329335Linear FluorescentUnknown630238Linear Fluorescent FixturesF36SEF30T12Fluorescent, (6) 36", STD lampMag-ES630238
331514Linear FluorescentT5HO Linear Fluorescent654351T5Linear Fluorescent FixturesF46GHLF48T5/HOFluorescent, (6) 48", STD HO T5 lampElectronic654351
406656Linear FluorescentT5HO Linear Fluorescent654364T5Linear Fluorescent FixturesF46GHL-HF54T5/HOFluorescent, (6) 48", STD HO T5 lamp HLO (BF: .96-1.2)Electronic654364
501507Linear FluorescentUnknown634216Linear Fluorescent FixturesF46EEF40T12/ESFluorescent, (6) 48", ES lampMag-ES634216
502508Linear FluorescentUnknown634186Linear Fluorescent FixturesF46ELF40T12/ESFluorescent, (6) 48", ES lampElectronic634186
503509Linear FluorescentUnknown634236Linear Fluorescent FixturesF46ESF40T12/ESFluorescent, (6) 48", ES lampMag-STD634236
505511Linear FluorescentT8 Linear Fluorescent632175T8Linear Fluorescent FixturesF46ILLF32T8Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)Electronic632175
506512Linear FluorescentT8 Linear Fluorescent632156T8Linear Fluorescent FixturesF46ILL-RF32T8Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, RLO (BF< .85)Electronic632156
507513Linear FluorescentT8 Linear Fluorescent632182T8Linear Fluorescent FixturesF46LLF32T8Fluorescent, (6) 48", T-8 lamp, NLO (BF: .85-.95)Electronic632182
509515Linear FluorescentUnknown640258Linear Fluorescent FixturesF46SEF40T12Fluorescent, (6) 48", STD lampMag-ES640258
510516Linear FluorescentUnknown640282Linear Fluorescent FixturesF46SSF40T12Fluorescent, (6) 48", STD lampMag-STD640282
554510Linear FluorescentUnknown HO655405Linear Fluorescent FixturesF46EHSF48T12/HO/ESFluorescent, (6) 48", ES HO lamp (3.5' lamp)Mag-STD655405
635644Linear FluorescentT8 Linear Fluorescent659328T8Linear Fluorescent FixturesF86ILLF96T8Fluorescent, (6) 96", T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)Electronic659328
636645Linear FluorescentUnknown660369Linear Fluorescent FixturesF86EEF96T12/ESFluorescent, (6) 96", ES lampMag-ES660369
637646Linear FluorescentUnknown660330Linear Fluorescent FixturesF86ELF96T12/ESFluorescent, (6) 96", ES lampElectronic660330
639648Linear FluorescentT8 Linear Fluorescent632222T8Linear Fluorescent FixturesF46ILL-HF32T8Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, HLO (BF: .96-1.2)Electronic632222
640649Linear FluorescentT8 Linear Fluorescent632255T8Linear Fluorescent FixturesF46ILL-VF32T8Fluorescent, (6) 48", T-8 lamp, Instant Start Ballast, VLO (BF: >1.2)Electronic632255
641650Linear FluorescentT8 Linear Fluorescent632158T8Linear Fluorescent FixturesF46SILF30T8Fluorescent, (6) 48", 30W T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)Electronic632158
642651Linear FluorescentT8 Linear Fluorescent632144T8Linear Fluorescent FixturesF46SSILF28T8Fluorescent, (6) 48", 28W T-8 lamp, Instant Start Ballast, NLO (BF: .85-.95)Electronic632144
674643Linear FluorescentUnknown HO695721Linear Fluorescent FixturesF86EHSF96T12/HO/ESFluorescent, (6) 96", ES HO lampMag-STD695721

<tbody>
</tbody>
Wattage








Sheet to add the information

ABCDEFGHIJKLMNO
6Lighting CategoryLighting Subcategory# of Lamps per FixtureLamp WattageFixture WattageLamp TypeFIXTURE TypeFIXTURE CODELAMP CODEDESCRIPTIONBALLASTLAMP / FIXTWATT / LAMPWATT / FIXT
7High Intensity Discharge

<tbody>
</tbody>
ADD

Workbook Defined Names
NameRefers To
Type=OFFSET(CODES!$V:$V, 0, 0, COUNTA(CODES!$V:$V))

<tbody>
</tbody>

<tbody>
</tbody>



V
1Lighting Category
2CFL
3Circular Fluorescent
4Exit Sign
5LED
6Incandescent
7Linear Fluorescent
8High Intensity Discharge
9Induction Fluorescent
10U_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.

ABCDEFGHIJKLMNO
1OrderLighting CategoryLighting Subcategory# of Lamps per FixtureLamp WattageFixture WattageLamp TypeFIXTURE TypeFIXTURE CODELAMP CODEDESCRIPTIONBALLASTLAMP / FIXTWATT / LAMPWATT / FIXT
759862High Intensity DischargeMetal Halide13243Metal Halide FixturesMH32/1MH32Metal Halide, (1) 32W lampCWA13243
760870High Intensity DischargeMetal Halide13544Metal Halide FixturesMH35/1MH35Metal Halide, (1) 35W lampCWA13544
761871High Intensity DischargeMetal Halide15072Metal Halide FixturesMH50/1MH50Metal Halide, (1) 50W lampCWA15072
762872High Intensity DischargeMetal Halide17095Metal Halide FixturesMH70/1MH70Metal Halide, (1) 70W lampCWA17095
763854High Intensity DischargeMetal Halide1100128Metal Halide FixturesMH100/1MH100Metal Halide, (1) 100W lampCWA1100128
764856High Intensity DischargeMetal Halide1150190Metal Halide FixturesMH150/1MH150Metal Halide, (1) 150W lampCWA1150190
765858High Intensity DischargeMetal Halide1175215Metal Halide FixturesMH175/1MH175Metal Halide, (1) 175W lampCWA1175215
766860High Intensity DischargeMetal Halide1200232Metal Halide FixturesMH200/1MH200Metal Halide, (1) 200W lampCWA1200232
767861High Intensity DischargeMetal Halide1250295Metal Halide FixturesMH250/1MH250Metal Halide, (1) 250W lampCWA1250295
768863High Intensity DischargeMetal Halide1300342Metal Halide FixturesMH300/1MH300Metal Halide, (1) 300W lampCWA1300342
769864High Intensity DischargeMetal Halide1320365Metal Halide FixturesMH320/1MH320Metal Halide, (1) 320W lampCWA1320365
770865High Intensity DischargeMetal Halide1350400Metal Halide FixturesMH350/1MH350Metal Halide, (1) 350W lampCWA1350400
771866High Intensity DischargeMetal Halide1360430Metal Halide FixturesMH360/1MH360Metal Halide, (1) 360W lampCWA1360430
772867High Intensity DischargeMetal Halide1400458Metal Halide FixturesMH400/1MH400Metal Halide, (1) 400W lampCWA1400458
773869High Intensity DischargeMetal Halide1450508Metal Halide FixturesMH450/1MH450Metal Halide, (1) 450W lampCWA1450508
774873High Intensity DischargeMetal Halide1750850Metal Halide FixturesMH750/1MH750Metal Halide, (1) 750W lampCWA1750850
775868High Intensity DischargeMetal Halide2400916Metal Halide FixturesMH400/2MH400Metal Halide, (2) 400W lampCWA2400916
776855High Intensity DischargeMetal Halide110001080Metal Halide FixturesMH1000/1MH1000Metal Halide, (1) 1000W lampCWA110001080
777857High Intensity DischargeMetal Halide115001610Metal Halide FixturesMH1500/1MH1500Metal Halide, (1) 1500W lampCWA115001610
778859High Intensity DischargeMetal Halide118001875Metal Halide FixturesMH1800/1MH1800Metal Halide, (1) 1800W lampCWA118001875

<tbody>
</tbody>
Wattage

This is how I was doing it before I gave up:
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABAC
1Lighting Category ListCFL ListCircular Fluorescent ListDelamp ListExit Sign ListHigh Intensity Discharge ListIncandescent ListInduction Fluorescent ListLED ListLinear Fluorescent ListNone ListU-Tube Fluorescent ListLamps per Fixture List_0_1
2CFLCFL FixtureCircular FluorescentDelampExit SignHigh Pressure SodiumHalogen IncandescentInduction FluorescentLED FixtureT5 Linear FluorescentDNDU-Tube Fluorescent_00_0.5
3Circular FluorescentCFL Screw-InExit Sign LEDMercury VaporStandard IncandescentLED Screw-InT5HO Linear FluorescentN/A_1_1.5
4DelampCFL Screw-In LEDMetal HalideLED Exit SignT8 Linear Fluorescent_2_2
5Exit SignCold Cathode CFLPulse Start Metal HalideCFL Screw-In LEDT8HO Linear Fluorescent_3_3
6High Intensity DischargeT10 Linear Fluorescent_4_4
7IncandescentT12 Linear Fluorescent_5_5
8Induction FluorescentUnknown_6_6
9LEDUnknown HO_8_7
10Linear Fluorescent_9_7.5
11U_Tube Fluorescent_10_8
12None_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.
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Watch MrExcel Video

Forum statistics

Threads
1,114,116
Messages
5,546,035
Members
410,721
Latest member
adi772
Top