Create Multiple Dependent Drop-Down Lists in Excel (on Every Row)
26 March 2020 Drop Down Lists in Excel (Dependent, Unique Lists and other data validation lists)
Join 400,000+ professionals in our courses: https://www.xelplus.com/courses/
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.
★ My Online Excel Courses ► https://www.xelplus.com/courses/
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.
★ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/
00:00 Multiple Dependent Drop Down Lists in Microsoft Excel
05:55 Fixed Number of Choices in the Dependent Drop-Down
07:15 Dynamic number of Choices in Drop-Down
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
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
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
✉ Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/
RESOURCES I Recommend: https://www.xelplus.com/resources/
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 #Excel #LeilaGharani
-
3:15:49
The Breanna Morello Show
17 hours agoMike Benz in Studio: Hillary Clinton & John Kerry Declare War on First Amendment | The Breanna Morello Show
86.7K42 -
1:54:42
Kimberly Guilfoyle
15 hours agoSave America: President Trump in Erie, PA- Replay September 29
115K51 -
59:12
Flyover Conservatives
1 day agoUN Power Grab… Are They Using Google to Control Dissent and Push Propaganda? - Alex Newman; Economic Update - Dr. Kirk Elliott | FOC Show
77.4K9 -
1:13:55
Abigail Joy
9 hours agoYou're Not Bullish Enough! - Human 1.0 w/ Abigail Joy - September 30 2024 - LIVE STREAM
60.9K4 -
14:35
China Uncensored
10 hours agoI Wasn’t Expecting THIS Would Turn China Against Xi Jinping
67.6K20 -
55:21
LFA TV
1 day agoNetanyahu Understands War | Trumpet Daily 9.30.24 9PM EST
59K21 -
1:13:17
We Like Shooting
22 hours agoDouble Tap 377 (Gun Podcast)
45.7K -
1:00:44
The StoneZONE with Roger Stone
10 hours agoWill Hurricane Helene Affect The Election In 35 DAYS? The StoneZONE w/ Roger Stone
48.1K33 -
3:12:23
Tundra Gaming Live
16 hours agoThe Worlds Worst Horror Streamer Has Heart Attack Playing Phasmophobia
37.1K -
56:22
Patriots With Grit
14 hours agoReclaiming The Republic One County At A Time | Natalie Scholl
29.6K1