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
<tbody>
</tbody>
Sheet to add the information
<tbody>
</tbody>
<tbody>
</tbody>
<tbody>
</tbody>
<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.
<tbody>
</tbody>
This is how I was doing it before I gave up:
<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>
Is there an easier solution? Thank you in advance. I apologize if I did not post the pictures correctly.
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
<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.