Drop down list with conditions

[baidya91]

How to create a drop down list with conditions as follows:

IF B1= 1981,
C1 = [TABLE="width: 303"]
<colgroup><col></colgroup><tbody>[TR]
[TD]800-15-920-18-1118-21-1265[/TD]
[/TR]
[TR]
[TD]660-40-1100-50-1600[/TD]
[/TR]
[TR]
[TD]550-25-625-30-745-35-780-40-1220-50-1470[/TD]
[/TR]
[TR]
[TD]500-20-540-25-640-30-820-40-920[/TD]
[/TR]
[TR]
[TD]440-20-560-25-785-30-875-35-1050-40-1170[/TD]
[/TR]
[TR]
[TD]300-10-400-15-565-20-685[/TD]
[/TR]
[TR]
[TD]2600-55-2985-80-3525-65-4175

IF B1 = 1990

C1 =
[TABLE="width: 303"]
<colgroup><col></colgroup><tbody>[TR]
[TD]4500-150-5250-175-7000-200-8800-225-9700[/TD]
[/TR]
[TR]
[TD]2200-80-3000-100-4000[/TD]
[/TR]
[TR]
[TD]1780-65-2040-80-3080-100-3780[/TD]
[/TR]
[TR]
[TD]1640-65-2095-80-3135-100-3635[/TD]
[/TR]
[TR]
[TD]1420-45-1555-55-1720-65-2305-75-3130[/TD]
[/TR]
[TR]
[TD]1040-25-1215-30-1485-35-1590-40-1670-50-1920[/TD]
[/TR]
[TR]
[TD]3600-100-4200-125-5700-150-7050[/TD]
[/TR]
[TR]
[TD]3350-90-3800-100-4700-125-6325[/TD]
[/TR]
[TR]
[TD]2600-55-2985-60-3525-65-4175[/TD]
[/TR]
[TR]
[TD]2700-60-3120-65-3770-70-4400
IF B1 = 1998
C1 =
[TABLE="width: 285"]
<colgroup><col></colgroup><tbody>[TR]
[TD]8000-275-13500[/TD]
[/TR]
[TR]
[TD]6000-225-7800-250-9800-275-12000[/TD]
[/TR]
[TR]
[TD]5500-200-6300-225-8325-250-11325[/TD]
[/TR]
[TR]
[TD]4800-175-5850-200-6650-225-8675-250-10925[/TD]
[/TR]
[TR]
[TD]4650-150-5100-175-6325-200-7925-225-10175[/TD]
[/TR]
[TR]
[TD]4500-150-5250-175-7000-200-8800-225-9700
IF B1 = 2009
C1 -
[TABLE="width: 283"]
<colgroup><col></colgroup><tbody>[TR]
[TD]PB4-BP-9000-40500-GP-RS-5400-AGP-RS-200[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-4800-AGP-RS-200[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-4800[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-4700[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-4400[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-4100[/TD]
[/TR]
[TR]
[TD]PB4-BP-9000-40500-GP-RS-3900[/TD]
[/TR]
[TR]
[TD]PB2-BP-5400-25200-GP-RS-2600[/TD]
[/TR]
[TR]
[TD]PB2-BP-5400-25200-GP-RS-2900[/TD]
[/TR]
[TR]
[TD]PB1-BP-4900-16200-GP-RS-1700[/TD]
[/TR]
[TR]
[TD]PB1-BP-4900-16200-GP-RS-1800

FOR EXAMPLE, IF B1 = FRUIT,
C1 =
APPLE
ORANGE
GUAVA
MANGO........[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Easiest way is with named ranges. Create a sheet named "Lists". Put your first list in column A. Then select the range and create a named range called "List1981". You can either just enter the name in the address box to the left of the formula bar, or go to the Formulas tab and click Define Name. Repeat for the other ranges. Now go back to your main sheet, select C1, from the Data tab select Data Validation > List > and for the formula enter:

=INDIRECT("List"&B1)


If your lists might change, it's possible to make the named ranges dynamic, so if you add an entry to the end, it will show up in the drop-down.

 

[baidya91]

Easiest way is with named ranges. Create a sheet named "Lists". Put your first list in column A. Then select the range and create a named range called "List1981". You can either just enter the name in the address box to the left of the formula bar, or go to the Formulas tab and click Define Name. Repeat for the other ranges. Now go back to your main sheet, select C1, from the Data tab select Data Validation > List > and for the formula enter:

=INDIRECT("List"&B1)


If your lists might change, it's possible to make the named ranges dynamic, so if you add an entry to the end, it will show up in the drop-down.

Thanks a lot...

 

[Eric W]

You're welcome.