Multilevel Drop Down Lists

1

Cascading drop downs are really useful when you have categories and sub-categories that you want to be able to specify within an Excel worksheet.  In the example shown in the video our first drop down allows us to specify a location – this could be an office location and the second drop down an employee.  If I select “London” in the first drop down I only want the London employees to appear in the second – I don’t want the full list of employees.  So in effect the first drop down needs to filter the second.

Just to walk you through the example shown in the video. Start off in the first sheet of the workbook by typing in two column headings Location in A1 and Employees in A2. In the second sheet, type the following data – or make up your own locations and names.

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

Please make sure that the location names in cells A2 – A5 are typed in exactly the same as the location headings in B1 to D1.

2

Next select cells B1 through to D6.  We are going need to name these columns.  Naming columns makes it much easier to refer to them later on in the task. To name the columns click the Formulas tab on the Ribbon and in the Defined Names tab click the Create from Selection button.  In the resulting dialog box make sure Top Row is the only thing ticked: we are saying name these columns based on the top row values – ie London, Birmingham and Bristol.

3

Now move back to the first sheet.  Click in cell A2, under your Location heading.  Next select the Data tab on the Ribbon and in the the Data Tools group, click the Data Validation button.  Data validation allows you to specify valid entries for a cell.  One way of restricting users to entering ‘valid’ values is to provide a drop down list.  In the Allow menu select List.  In the Source box specify the range of cells in the your second sheet that contains your offices – in my example this would be A2:A5; so the range would be =Sheet2!$A$2:$A$4 (note the equals sign before the range – this is necessary!!!).  Click OK to confirm.

4

You have your first drop down list, now on to the second.  Click in cell B2 in the first sheet under your heading Employees.   Now select the Data tab on the Ribbon and in the the Data Tools group, click the Data Validation button.  In the Allow menu select List. In the source box we are going to use the INDIRECT function to specify that we only want to show values from the relevant named list in sheet 2 – so either the London, Birmingham or Bristol list.

INDIRECT returns the reference specified by a text string: the reference we want to return is the relevant location column and the text string is specified by the value selected in cell A2 (our first drop down). This only works because the values in our drop down list in cell A2 correspond exactly to the named references in sheet 2 that we created earlier on.  The formula to use is =INDIRECT($A$2).  

If you haven’t yet selected in a location from the drop down in A1, you will get the following warning.

Click on Yes to continue.  All this is saying is that there isn’t yet a value in A1 that the INDIRECT function can calculate on.  Your drop downs will work once you have made your initial selection in A1.

If the warning doesn’t appear, just click OK to confirm and you will have your cascading drop downs.