Dependent list:

In this tip, we will discuss how to create a dependent list.

The file consists of two sheets, “Choice Lists” and “Countries” (1). The data fields are “Continent”, “Europe”, “Africa”,”Asia”,” North_America”,”South_America” and “Oceania” (2).

We first assign names to the different data fields, see Course Excel 2007 Lesson 12, Course Excel 2010 Lesson 15 for “Naming the Range”.
Click in cell A2, press Ctrl + Shift + (downward arrow) in the cell area and give it a name, for eg.: “Continents”.

Give the cell area B2 to B55, the name “Europe”‘, C2 to C54 the name “Africa”, D2 to D49, “Asia”, E2 to E24, “North_America”, F2 to F13, “South_America ” and G2 to G15, “Oceania”.

Click on the “Choice Lists” sheet tab and click in cell A2. Select the “Data” tab in the “Ribbon” and click the “Data Validation” button.

In the “Data Validation” (1) dialog box, select “List” in the “Allow” box (2), place the cursor in the “Source” (3), press the function key F3, in the “Paste Name” dialog box (4) select the newly assigned range names that appear. Click on “Continents” (5) and then click OK.  Click OK again.

Select a continent from the list in the cell A2 eg, “Europe” (1).

Now to determine the list that is to be displayed in cell C2 we use “Data Validation” along with the INDIRECT function.

The INDIRECT function returns a reference to a cell containing an A1-style reference, an R1C1-style reference, a reference to a cell that contains a string, or as in our example, a name defined as a reference.

Click in cell C2, and go to “Data” on the ribbon, click on “Data Validation”, select “Allow” for the “List” text box and type the formula = INDIRECT (A2) in the “Source:” text box and click OK.
Our dependent list box is ready.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats