Excel Drop Downs from Dynamic Arrays

Written by Debra Dalgleish from Contextures Blog

Select a region name in one Excel drop down list. Then, in the next drop down list, select from a list of employees who work in that region. You’ve seen this dependent drop down trick before, but watch my latest video below, to see a new twist on this technique.

Dependent Drop Downs

For a quick peek at the new twist, this animated screen shot shows the dependent drop down lists in action.

When you select a region name in cell F2, the drop down in cell G2 shows that region’s employees.

Dynamic Arrays

In cells D7 and F2, I used the new Excel functions, UNIQUE, SORT, and FILTER, to create dynamic arrays for the drop down lists.

If you’re using Excel for Office 365, with monthly updates, you should have these new functions now.

If your version of Excel doesn’t have these function, or dynamic arrays, go to my Contextures site, for other ways to create dependent drop down lists.

Video: Drop Downs – Dynamic Arrays

To see all the details for setting up these dependent drop downs, using dynamic arrays, watch this video. The timeline is below the video.

Video Timeline

0:00 Introduction
0:31 Create a Unique List of Regions
2:38 Make a Region Drop Down
3:30 Create an Employee List
5:50 Make an Employee Drop Down
7:35 Get the Workbook
Get the Sample Workbook

Get the Written Steps

Maybe you don’t like watching videos. Would you rather follow the written steps for this technique, with lots of screen shots?

If so, then go to the dynamic array drop downs page on my Contextures site. All the steps and formulas are described there, with screen shots for most of the steps.

Get the Workbook

To get the free workbook for this video, go to my Contextures website, and download it there. The zipped workbook is in xlsx format, with no macros.

Remember though, this technique will only work if your version of Excel for Office 365 has the dynamic arrays feature.

Hide Used Items in Drop Down

To see another example of using dynamic arrays for drop down lists, go to the Hide Used Items page on my Contextures site.

In the Employee column, after a name has been selected, it disappears from the drop down list.

The list of available employees is created with a dynamic array on another sheet. Here is the formula in cell D2:

=SORT(FILTER(tblEmp[EmpList], COUNTIF(tblSched[Employee], tblEmp[EmpList])=0))

___________________________