Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)
Learn how you can create MULTIPLE dependent data validation lists in Excel on the SAME sheet. In this video I'll show you a solution that requires ONE single formula. This formula also excludes blank cells on the bottom of the data validation list.
In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.
The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:
Fixed number of Choices in the dependent drop-down (05:55)
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.
Dynamic number of Choices in Drop-Down (07:15)
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-dependent-drop-down-every-row
LINK to related OFFSET video: https://youtu.be/RPTQjbk2qy4
Complete Excel Data Validation Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Time Stamps:
00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
07:16 Eliminate Empty Rows in Drop Down List
10:21 Copy Down Data Validation
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Видео Create Multiple Dependent Drop-Down Lists in Excel (on Every Row) канала Leila Gharani
In our example the selection of a division will drive the available choices for an app in an Excel data validation list. We'll use the Excel OFFSET function and the MATCH function for the dependent drop down selection. This way you can have as many categories as you like.
The challenge we have is that the number of choices inside the dependent drop down may vary depending on the division. I'll show you 2 solutions for this:
Fixed number of Choices in the dependent drop-down (05:55)
If you do not mind empty spaces (blank cells) at the bottom of the drop down you can fix the height argument in the OFFSET function to a specific (maximum) number of choices.
Dynamic number of Choices in Drop-Down (07:15)
If we want to restrict the number of visible cell values inside the dependent drop-down we can expand the formula to get rid of the blank cells on the bottom. This way we can eliminate empty spaces in the drop down selection. We will use another OFFSET formula inside a COUNTA formula to dynamically count the number of available choices.
⯆ DOWNLOAD the workbook here: https://www.xelplus.com/excel-dependent-drop-down-every-row
LINK to related OFFSET video: https://youtu.be/RPTQjbk2qy4
Complete Excel Data Validation Playlist: https://www.youtube.com/playlist?list=PLmHVyfmcRKywYhC1Q9eZqR7D-_cdiwl6y
★ My Online Excel Courses ► https://www.xelplus.com/courses/
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
EXCEL RESOURCES I Recommend: https://www.xelplus.com/resources/
Get Office 365: https://microsoft.msafflnk.net/15OEg
Microsoft Surface: https://microsoft.msafflnk.net/c/1327040/451518/7593
GEAR
Screen recorder: http://techsmith.pxf.io/c/1252781/347799/5161
Main Camera: https://amzn.to/3a5ldBs
Backup Camera: https://amzn.to/2FLiFho
Main Lens: https://amzn.to/39apgeD
Zoom Lens: https://amzn.to/3bd5pN7
Audio Recorder: https://amzn.to/2Uo5rLm
Microphone: https://amzn.to/2xYy9em
Lights: http://amzn.to/2eJKg1U
More resources on my Amazon page: https://www.amazon.com/shop/leilagharani
Time Stamps:
00:00 Dependent Drop-Down Lists in Excel on EVERY Row
01:20 Creating the First Drop Down
01:58 Creating the Dependent Drop Down List
07:16 Eliminate Empty Rows in Drop Down List
10:21 Copy Down Data Validation
Let’s connect on social:
Instagram: https://www.instagram.com/lgharani
Twitter: https://twitter.com/leilagharani
LinkedIn: https://at.linkedin.com/in/leilagharani
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#MsExcel
Видео Create Multiple Dependent Drop-Down Lists in Excel (on Every Row) канала Leila Gharani
Показать
Комментарии отсутствуют
Информация о видео
Другие видео канала
Drop Down Lists in Excel - Masterclass (incl. Dynamic, Dependent & Searchable Drop Down Lists)Searchable Drop Down List in Excel (Very Easy with FILTER Function)Excel OFFSET Function for Dynamic Calculations - Explained in Simple StepsExcel Pivot Tables EXPLAINED in 10 Minutes (Productivity tips included!)SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)Build Impressive Charts: It's NOT your usual Bar Chart (Infographics in Excel)Return Multiple Match Results in Excel (2 methods)Discover What XLOOKUP Can Do For YOU (R.I.P. Excel VLOOKUP)Excel DGET Function Solves 2 of Your VLOOKUP ProblemsHow To Create Multiple Dependent Drop Down Lists In Excel4 SMART Ways to use Custom Formatting instead of Conditional Formatting in Excel - Part 1SUPER EASY Excel Data Entry Form (NO VBA)Create SMART Drop Down Lists in Excel (with Data Validation)How to use Excel Index Match (the right way)MS Excel - Dependent Dropdown List6 FREE Add-Ins for Excel to Start Using NowHow To | Create Dependent Drop-down Lists in ExcelRealistic Case: Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in 1st ListHow Power Query Will Change the Way You Use ExcelGoogle Sheets - Drop Down List, 2 Dependent Dropdown Lists