This is a very simple way to make primary drop-down list that affects an other secondary drop-down list in Excel:
Step 1
Click the "+" sign at the bottom of Excel 2013 to create a new sheet to use as a data source.
Step 2
Enter the values you need for the first drop-down list in column A on the new sheet. These values should begin with a letter or underscore and not contain spaces.
Step 3
Enter the respective secondary values in the next columns for each value entered in column A. For example, if you entered "Speakers," "Adapters" and "Cables" in cells A1 through A3, fill-in the appropriate values in columns B through D. Because you have three categories, you need three secondary columns, one as a secondary source list for each category. The secondary columns have no naming restrictions and can contain a different number of values.
Step 4
Click and drag your mouse across the data cells in column A to select them. Click "Formulas," "Define Names" and then "Define Names."
Step 5
Enter a descriptive name for the cells and click "OK." The name follows the same restrictions as described earlier. You can use varying capitalization in the name to help identify separate words, such as "ProductCategories," but you do not need to mirror this capitalization when later referencing the defined range.
Step 6
Define names for each secondary data list using the Define Names feature that was described earlier. The name for these lists should mirror the value of the main categories. In the previous example, cells B1 through B4 should be named "Speakers," cells C1 through C8 should be named "Adapters" and cells D1 through D5 should be named "Cables." These example ranges assume values are entered in each cell; you shouldn't include blank cells in the defined range.
Step 7
Click the original sheet tab and select the blank cell in which the primary drop-down list should appear.
Step 8
Click "Data," "Data Validation" and then "Data Validation" again.
Step 9
Click the "Allow" drop-down list and select "List."
Step 10
Enter "=ProductCategories" (without quotes here and throughout) in the Source field and click "OK." Replace "ProductCategories" with the name you defined for the primary source data. This creates the primary drop-down list that feeds the secondary list.
Step 11
Click the cell in which the secondary drop-down list should appear. Select "Data," "Data Validation," "Data Validation."
Step 12
Click the "Allow" drop-down list and select "List."
Step 13
Source :How to Make a Drop-Down List Change Depending on Selection in a Different List in Microsoft Excel Enter "=Indirect(A1)" and click "OK." Change "A1" to reflect the cell address where the primary drop-down list appears. If you receive an error, disregard it; it only appears because no option has been selected from the primary list yet. Once you select any option from the primary list, the secondary list changes to reflect the associated values.
download an example here...
No comments:
Post a Comment