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
___________________
Hi Debra!!
I was struggling with a depending dropdown lists, and remembered you wrote something about it, and ot worked just fine!!!
is there a way to make this work within a table? I mean, I have a column with Attibutes and another one with values for those attributes. And another table where I select an attribute, from the unique list obtained from the 1st table, and wanted to select a value narrowed from the Values column from the 1st table.
on the first row of the second table, this works great. But when entering the second row, the dropdown on Values displays the selection from the Attributes fist row…
A penny for your thoughts….
Thanks!
Martin
Thanks, Martin, and you could try one of the dependent drop down techniques from these pages on my Contextures site:
https://www.contextures.com/xlDataVal13.html
https://www.contextures.com/exceldatavaldependindextables.html