![]() The SORT function arranges the names in alphabetical order (if you wish to preserve the existing order, remove it from the formula). Here, we utilize the FILTER function to filter the employees that belong to a particular department ($E$3:$E$15=H$2). To pull the manager names, the formula in H3 is: This way, we've got the items for our main drop-down list: Please notice that the formula needs to be entered just in one cell, and the results spill into neighboring cells automatically (this feature is called a spill range). Finally, TRANSPOSE changes the output orientation from vertical to horizontal. names will appear in the same order as in your source table). The SORT function arranges the results in alphabetical order so that the items of your main list will be sorted from A to Z (if you don't want that, you can remove SORT from the formula, and the dept. Here, the UNIQUE function extracts all the different departments from E3:E15. To get the departments, enter this formula in H2. To automate the work and prevent human errors, we will be using the following formulas. name there will be a list of employees working in that specific department. For this, we are going to create some sort of preparation table that will list all different department names in the header row, and under each dept. So, our first step is to structure the original data according to our needs. The source data for drop-down lists often come from different sources and are organized differently. ![]() If you change the selection in the main list, the names in the dependent drop-down will update accordingly. in the first list only displays Managers for that selected department in the second list. In our main table on the left, we want to have two picklists in each row, so that selecting a Dept. Source dataįor starters, let's get some source data to work on. In pre-dynamic versions of Excel, please use the traditional approach to creating multiple dependent drop down lists. ![]() Important note! Because this solution relies on the dynamic array feature, it is only applicable in Excel for Microsoft 365 and Excel 2021.
0 Comments
Leave a Reply. |