Drop down list with conditions

[baidya91]

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

IF B1= 1981,
C1 =

800-15-920-18-1118-21-1265

660-40-1100-50-1600

550-25-625-30-745-35-780-40-1220-50-1470

500-20-540-25-640-30-820-40-920

440-20-560-25-785-30-875-35-1050-40-1170

300-10-400-15-565-20-685

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

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.