How To Add Drop Down List In Excel 2010

One of the most useful features of data validation is the ability to create a dropdown list that let users select a value from a predefined list. Dropdown lists make it piece of cake for users to enter simply data that meets your requirements.

Dropdown lists are easy to create and use. But once you start to use dropdown menus to your spreadsheets, you'll inevitably run into a challenge: how can you make the values in i dropdown listing depend on the values in some other? In other words, how tin you make a dropdown listing dynamic?

Hither are some examples:

• a list of cities that depends on the selected state
• a list of flavors that depends on type of water ice cream
• a list of models that depends on manufacturer
• a list of foods that depends on category

These kind of lists are chosen dependent dropdowns, since the listing depends on another value. They are created with data validation, using a custom formula based on the INDIRECT function and named ranges. This may sound complicated, merely information technology is actually very unproblematic, and a great instance of how INDIRECT can be used.

Read on to see how to create dependent dropdown lists in Excel.

Dependent dropdown example

In the example shown beneath, column B provides a dropdown menu for food Category, and column C provides options in the chosen category. If the user selects "Fruit", they meet a listing of fruits, if they select "Nut", they see a list of nuts, and if they select "Vegetable", they see a listing of vegetables.

The data validation in column B uses this custom formula:

And the data validation in column C uses this custom formula:

Where the worksheet contains the following named ranges:

category = E4:G4
vegetable = F5:F10
nut = G5:G9
fruit = E5:E11

How this works

The primal to this technique is named ranges + the INDIRECT office. INDIRECT accepts text values and tries to evaluate them as cell references. For example, INDIRECT will take the text "A1" and turn information technology into an bodily reference:

Similarly, INDIRECT will convert the text "A1:A10" into the range A1:A10 within some other function:

At first glance, y'all might detect this construction abrasive, or even pointless. Why complicate a nice uncomplicated formula with INDIRECT?

Rest bodacious, in that location is method to the madness :)

The beauty of INDIRECT is that information technology lets y'all apply text exactly like a cell reference. This provides 2 key benefits:

1. You tin can gather a text reference within a formula, which is handy for sure kinds of dynamic references.
2. Yous can choice upwards text values on a worksheet, and use them like a cell reference in a formula.

In the example on this page, we're combining the latter idea with named ranges to build dependent dropdown lists. INDIRECT maps text to a named range, which is so resolved to a valid reference. So, in this example, we're picking up the text values in cavalcade B, and using INDIRECT to convert them to cell references by matching existing named ranges, like this:

B6 resolves to the text "nut" which resolves to the range G5:G9.

How to fix upwardly dependent dropdown lists

This section describes how to set upwardly the dependent dropdown lists shown in the example.

1. Create the lists you demand. In the instance, create a listing of fruits, nuts, and vegetables in a worksheet.

2. Create named ranges for each list: category = E4:G4, vegetable = F5:F10, nut = G5:G9, and fruit = E5:E11.

Important: the values in E4, F4, and G4 must friction match the concluding iii named ranges above (vegetable, nut, and fruit). In other words, you must make sure that the named ranges yous created are named to match the values that will appear in the Category dropdown list.

iii. Create and test a data validation rule to provide a dropdown list for Category using the post-obit custom formula:

Note: just to be clear, the named range "category" is used for readability and convenience only – using a named range here is not required. Too notation data validation with a list works fine with both horizontal and vertical ranges – both will exist presented as a vertical dropdown carte.

4. Create a data validation rule for the dependent dropdown list with a custom formula based on the INDIRECT function:

In this formula, INDIRECT just evaluates values in column B as references, which links them to the named ranges previously defined.

5. Exam the dropdown lists to make certain they dynamically respond to values in column B.

Note: the approach we are taking here is non case-sensitive. The named range is chosen "nut" and the value in B6 is "Nut" but the INDIRECT part correctly resolves to the named range, even though case differs.

Dealing with spaces

Named ranges don't allow spaces, so the usual convention is to use underscore characters instead. So, for example, if you lot desire to create a named range for ice cream, you would use ice_cream.This works fine, just dependent dropdown lists will break if they effort to map "ice foam" to "ice_cream". To gear up this trouble yous can use a more than robust custom formula for data validation:

This formula all the same uses INDIRECT to link the text value in A1 to a named range, merely earlier INDIRECT runs, the SUBSTITUTE function replaces all spaces with underscores. If the text contains no spaces, SUBSTITUTE has no outcome.

Practice file

The instance file is fastened below – bank check information technology out to see how information technology works. If you want to learn the technique yourself, I'd recommend you build a file of your own. The best way to learn is by doing.

Source: https://exceljet.net/dependent-dropdown-lists

Posted by: wisdomaboaccon.blogspot.com

Share this post