Multiple Dependent Drop-Down List in Excel | NEW Simple Method | Works with multiple rows
Vložit
- čas přidán 6. 07. 2024
- Learn the first steps to optimize any spreadsheet: www.excelpowerup.com/training
Take your Excel spreadsheets to the next level: www.excelpowerup.com/course
Get personalized professional support: www.excelpowerup.com/support
Creating multiple dependent drop-down lists in Excel can be streamlined with a new method that simplifies the process. This approach is particularly useful for tables with multiple levels of dependency, such as a category and sub-category structure, where the choices in one drop-down are contingent on the selection in another. Traditional methods often require complex table setups, extensive named ranges, and intricate formulas. However, this innovative solution promises an easier and more efficient way to manage dependent lists, potentially enhancing productivity and user experience in data management tasks.
Website: www.excelpowerup.com
Instagram: / rebekahoster
TikTok: / rebekahoster
LinkedIn: / rebekah-oster
00:00 Introduction
00:55 What is a Dependent Drop-Down List?
02:03 Create Table for Drop-Down Lists
02:47 Create Table of Options
03:51 Level 1 Drop-Down List
05:19 Level 2 Drop-Down List
10:31 Level 3 and Beyond - Věda a technologie
Fantastic! Very clearly explained.
Nice job. Thanks!
Thanks for watching!
thanks
You're welcome! Thank you for watching and commenting 😀⚡
you are awesome
Hello, this video was super helpful for me, thanks for sharing it! I created 3 dependent lists with lots of options within each list. I'm running into a problem though: as I get further down into my spreadsheet and choose from my first list I start loosing the different options I have in the second and third dependent lists that I used to see towards the beginning of the spreadsheet. I think this is because as I go down the possible options for each dependent list the formula goes down one row and the options start to get used up, so I'm left with less options to choose from each list. Would you know how I can fix this!? I tried copying and pasting the formula to each cell under the drop-down "Helper" instead of dragging down like you did, but that ends up only limiting me to the first thread of options of the dependent lists...I hope this explanation makes sense...Thanks!
Hi Rebekah! Really good method. I am having issue on the first step with the indirect function in the Data Validation. It works but i am still getting all the duplicates. Excel Version I have is Excel 365. No sure my its not working. I would appreciate the Help. Thanks!
Hi! Thank you for your comment!! I've done a little investigating, and it seems like this was a "secret" feature that came along with AutoComplete for Drop Down Lists. It could be that it's just not released for your Office 365 channel. I'm sorry! For Level 1, you could set up a helper column with =UNIQUE(Options[Level 1]). Then reference that cell in the Data Validation Source, just like I demonstrate for Levels 2 and 3.
Working on the formula for level 3. I am running into an issue where the 'working' tag is autogenerating when I select columns. If i manually type the code in, then excel doesn't recognize the formula as valid. But I am an excel newbie, so I could be doing something very silly. Anything thought on how to get the ' (Working[@[ ' portion of the formula to get added and to work?
Hello! Thank you for watching! It sounds like your Working data isn't formatted as an Excel Table. Using an Excel Table allows for Structured References aka using the square brackets to name the exact column. To do this, select anywhere on the data and click Insert -> Table. Then in the Table Design tab, name your table "Working" or whatever you want 🙂 If you want to see this in action, check out the free training here: www.excelpowerup.com/training
What not use a Pivot table?
Hi! Thanks for your comment 🙂 A PivotTable would be used to summarize data. This tutorial is for a method to speed up data entry. They are just different applications!
@@RebekahOster That makes a lot of sense. But it is a good video and has its place.
It's too complicated for me to learn, so I just apply it.😁
😉 How do you use dependent drop-down lists?