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))
___________________________
Excel Drop Downs from Dynamic Arrays
___________________
Excel Drop Downs from Dynamic Arrays is a post from Contextures Blog and is not allowed to be copied to other sites
Original source: https://contexturesblog.com/archives/2020/02/20/excel-drop-downs-from-dynamic-arrays/