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

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Watch MrExcel Video

Forum statistics

Threads
1,114,655
Messages
5,549,261
Members
410,905
Latest member
Extjel
Top