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