Create Cascading Drop Down Menu Box List Using Data Validation in Excel

Creating Cascading Drop Down Box Lists in Excel

Data validation is a function within Excel that allows you to set rules for cells in a worksheet that restrict the type of data than can be entered. One of the methods that data validation uses is to allow the user to specify a list of valid values.

This drop down box appears when you click in the cell and allows the end user to select a value from the list but not type in their own values. This is very useful for Excel databases where multiple end users may enter new records and you want to ensure that errors are avoided in terms of spelling and erroneous entries.

This tutorial goes one step further than setting up a simple list; it will show you how to set up two lists that interact with each other. When a value is chosen in the first list, the second list shows values that relate to that choice. In other words the second list is programmed to show subcategories of the value chosen in the first list.

1. Start by creating a new workbook with at least two worksheets.  In the second worksheet create the following lists starting in A1

Location	London	Birmingham	Bristol
London	John	Fred	Harry
Birmingham	Jack	Phillip	Paul
Bristol	Mark	Kate	Tim
	Andy	Sarah
		Joan

2. Name each list as follows, Location, London, Birmingham, Bristol.  The names you use have to be the same as the values in the location field. Do not include the column headers in the named ranges.

3. In the first worksheet, type the following, starting in A1

1. Location

   Employee

4. Now click into A2 and then click Data | Validation (Office 2003) or
Data Ribbon | Data Validation | Data Validation (Office 2007/10)

5. In the Data Validation box, select List from the Allow list.

6. Click into the Source box, press F3 on your keyboard and select Location from the Paste box. Click on OK in the Paste box and then OK in the Data Validation box.

7.  Select B2 and then click Data | Validation (Office 2003) or
Data Ribbon | Data Validation | Data Validation (Office 2007/10)

8. In the Data Validation box, select List from the Allow list.

9. Click into the Source box, and type =INDIRECT(A2)
The INDIRECT function returns the range reference represented by a text string, so in other words the location drop down list in A2 is being used as a list of range names.

10. Click on OK in the Data Validation box.

Your drop down list should now work properly.

NOTE: If your first drop down list contains values that contain spaces you will need to replace spaces with underscores to create valid references.  Use the substitute function if this is the case
 =INDIRECT(SUBSTITUTE(A2," ","_"))