Make Dynamic Dependent Drop Down Lists In Excel | Office 365
👇 LET'S CONNECT 👇
https://linktr.ee/excelupnorth
—————————————————————
⏰ Timestamps ⏰
00:00 Intro
00:27 Overview Of Data
00:42 How The Dependent #Dropdown Lists Will Work
01:17 Simulating A Dependent Dropdown List
01:50 Creating A Staging Area for Dynamic Dependent Dropdown List Data
02:13 Populating Options For The First Dropdown List
03:09 Populating Options For The Second Dropdown List
04:40 Creating The First Dropdown List
05:27 Creating The Dynamic Dependent Dropdown List Formula
10:12 Creating The Dynamic Dependent Dropdown List
10:41 Creating The Dynamic Dependent Dropdown List Again
12:55 Populating Original Source With More Data
14:02 Outro
—————————————————————
In this video, we will walk through an example of creating dynamic dependent dropdown lists in #Excel in a table containing three columns: Department, Role, and Job Title. The goal is to create dropdown lists that update based on the selections made in the previous columns.
Here's The Step-by-Step Guide:
1) Introduction to The Dropdown List Data: The dropdown list in the Department column will list all departments. Selecting a department will filter the Role column to show relevant roles. Similarly, selecting a role will filter the Job Title column to show relevant job titles.
2) Understanding Dependent Dropdown Lists: If the Engineering department is selected, the Role column should only display roles within Engineering. Further, selecting a specific role like Electrical Engineering should filter the Job Title column accordingly.
3) Set The Stage: Stage the data so that our dropdown lists will reference it correctly.
4) Creating the Department Dropdown: Use the UNIQUE function to get a distinct list of departments.
Wrap the UNIQUE function with the SORT function to alphabetize the list. Use the TOROW function to convert this dynamic column array into a dynamic row array. This dynamic array will expand or contract as departments are added or removed.
5) Populating Roles Based on Departments: Use the FILTER function to get a list of roles based on the selected department. The array argument will be the Role column from the Employees table, and the include argument will check the Department column for the value in cell E1. Copy this function to accommodate a growing list of departments.
6) Creating the Dropdown List for Departments: Select the cells to add the dropdown to. Go to Data Validation, choose List, and set the source to our dynamic department array.
• Creating the Dynamic Role Dropdown: Use the ADDRESS function to create a cell reference for the roles based on the selected department. Use the ROW and COLUMN functions to dynamically reference the cell containing the roles. Wrap the ADDRESS function with INDIRECT to return the values within this dynamic array. To make the formula dynamic, use the XMATCH function to find the position of a department within the department array, adjusting the column reference accordingly.
• Finalizing and Testing: After setting up the formula, cut and paste it into the Data Validation source box for the Role dropdown. Repeat similar steps for creating the Job Titles dropdown based on the selected role.
#microsoftambassador
Смотрите видео Make Dynamic Dependent Drop Down Lists In Excel | Office 365 онлайн без регистрации, длительностью часов минут секунд в хорошем качестве. Это видео добавил пользователь Excel Up North 04 Июнь 2024, не забудьте поделиться им ссылкой с друзьями и знакомыми, на нашем сайте его посмотрели 1,62 раз и оно понравилось 1 людям.