Hi Thanks so much for this it’s so easy - but what if I wanted to add the same colour twice in the same level? Just to put this into context I’m working in birthstones and someone might want to add garnet, amethyst, garnet? Many thanks!
This worked great!!!! Thank you for the detailed yet simple instructions. The only issue I had was I did not have the =torow formula available in Office 2021, so I just substituted it using the =transpose formula.
Unfortunately, a small thing bothers me. Video Time 13:55 - "To have enough Rows" ! I Wish i would be completely dynamic. But nice Video for practice Data Validation and the filter function. Thank you for that.
Thank you - this tutorial was very helpful - was getting a bit confused with the Options and Working titles being the same but I got used to it. Thank you very much for sharing .... much appreciated
It was killing me trying to find what I wanted with so many vids showing 2 validations or horizontal lists. Your was exactly what I wanted and easy to understand your logic. Thank you so much !!
Hi Rebekah - I have reached row 5777 in my sheet with the dropdowns on and notice that the dependencies cease to work from that point on. I also notice that my helper code (=TOROW(UNIQUE(FILTER(Options[NACS CATEGORY],Working2[@[DEPARTMENT NAME]]=Options[DEPARTMENT],"")))) only goes down to 5777 so it looks like the problem is related to the lookup code. Not sure if that makes sense but just asking if you have seen this before and can assist?
my Data Validation doesn't allow me to remove the $ on the number, which forces me to set the data validation for each row. Can you check again on this pls?
Hi Thanks so much for this it’s so easy - but what if I wanted to add the same colour twice in the same level? Just to put this into context I’m working in birthstones and someone might want to add garnet, amethyst, garnet? Many thanks!
Thanks. I will try it. How about data consistency if level 1 is changed? Any vba free method to do it? Conditional formatting warning maybe? Or something smarter?
Hi Rebekah, thank you for providing that example. One question I have: I plan on creating this for an excel sheet that will have thousands of lines. Will all these filter equals slow down the workbook too much?
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.
@@RebekahOster Hiii, im geting the same issue, can u explain further where should i put the " =UNIQUE(Options[Level 1])" is it in the data validation part?
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!
If I'm understanding correctly, the problem may be with the cell references. Like you said, when you go down the options start to get used up. In the video I always use Structured References, which are available if you are using Excel Tables. They look like this: Options[Level 2]. That will grab all the cells in the Level 2 column. And even when the formula is dragged down, the selection won't change! If you just selected that range though, and it looks like this: A2:J10, when you drag the formula down, it will change to A3:J11. (those cell references are made up, so I hope it makes sense!) The #1 solution is to convert your options data into an Excel Table. Check out my free training for more info (www.excelpowerup.com/training) ! You could also convert the references from relative to absolute by adding dollar signs like this: $A$2:$J$10.
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
This solution works but has the handicap that for every row in your worktable you create data in the two helper tabels. For smal files no problem but for excel files with thousands of rows you create la lot of data that's not going to be used anymore. That can be a great handicap!
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!
![Create multiple dependent drop-down lists in Excel [EASY]](http://i.ytimg.com/vi/daCvyt9E8s4/mqdefault.jpg)
This is the most incredible, easy, dynamic dependent dropdown solution I've ever seen. THANK YOU SO MUCH.
Wow, that is such high praise! Thanks for watching 😁
Hi Thanks so much for this it’s so easy - but what if I wanted to add the same colour twice in the same level? Just to put this into context I’m working in birthstones and someone might want to add garnet, amethyst, garnet? Many thanks!
This worked great!!!! Thank you for the detailed yet simple instructions. The only issue I had was I did not have the =torow formula available in Office 2021, so I just substituted it using the =transpose formula.
Unfortunately, a small thing bothers me. Video Time 13:55 - "To have enough Rows" ! I Wish i would be completely dynamic. But nice Video for practice Data Validation and the filter function. Thank you for that.
Thank you - this tutorial was very helpful - was getting a bit confused with the Options and Working titles being the same but I got used to it. Thank you very much for sharing .... much appreciated
I'll be using this to automate my time sheet as I work with several charge numbers under different projects. Thanks!
Hats 👒 off!
Brilliant excel expert!
It was killing me trying to find what I wanted with so many vids showing 2 validations or horizontal lists. Your was exactly what I wanted and easy to understand your logic. Thank you so much !!
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
Hi Rebekah - I have reached row 5777 in my sheet with the dropdowns on and notice that the dependencies cease to work from that point on. I also notice that my helper code (=TOROW(UNIQUE(FILTER(Options[NACS CATEGORY],Working2[@[DEPARTMENT NAME]]=Options[DEPARTMENT],"")))) only goes down to 5777 so it looks like the problem is related to the lookup code. Not sure if that makes sense but just asking if you have seen this before and can assist?
If the working table and Option table are in two different sheet of a workbook. Please share the changes in formulas to be put.
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
my Data Validation doesn't allow me to remove the $ on the number, which forces me to set the data validation for each row. Can you check again on this pls?
Absolutely Amazing and Super Clear Explanations. Had it implemented on my use case today ! 👍
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
How can we do this in google sheets?
Hi Thanks so much for this it’s so easy - but what if I wanted to add the same colour twice in the same level? Just to put this into context I’m working in birthstones and someone might want to add garnet, amethyst, garnet? Many thanks!
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
Thanks. I will try it. How about data consistency if level 1 is changed? Any vba free method to do it? Conditional formatting warning maybe? Or something smarter?
Using Name Manager
@@ShivAadesh ok but how?
Hi Rebekah, thank you for providing that example. One question I have: I plan on creating this for an excel sheet that will have thousands of lines. Will all these filter equals slow down the workbook too much?
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
Great tutorial...Wish I followed this one first :) Anyway to transpose this ?
Fantastic! Very clearly explained.
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.
@@RebekahOster Hiii, im geting the same issue, can u explain further where should i put the " =UNIQUE(Options[Level 1])" is it in the data validation part?
Is there a way to do this with the "Options" (data) on a separate workbook?
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
This is great! Is there a way to have the last level auto populate?
Easiest method ever.
For mine when I put another colour below the first colour it does not choose the level 2 options
I cover similar method with smart indirect. ruclips.net/video/KyxBV2y-zW8/видео.html
Terima kasih banyak
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!
If I'm understanding correctly, the problem may be with the cell references. Like you said, when you go down the options start to get used up. In the video I always use Structured References, which are available if you are using Excel Tables. They look like this: Options[Level 2]. That will grab all the cells in the Level 2 column. And even when the formula is dragged down, the selection won't change! If you just selected that range though, and it looks like this: A2:J10, when you drag the formula down, it will change to A3:J11. (those cell references are made up, so I hope it makes sense!) The #1 solution is to convert your options data into an Excel Table. Check out my free training for more info (www.excelpowerup.com/training) ! You could also convert the references from relative to absolute by adding dollar signs like this: $A$2:$J$10.
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
Thanks alot
This solution works but has the handicap that for every row in your worktable you create data in the two helper tabels. For smal files no problem but for excel files with thousands of rows you create la lot of data that's not going to be used anymore. That can be a great handicap!
Awesome! Thank you.
Nice job. Thanks!
Thanks for watching!
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.
Insightful
JEEZ, WHT A NIGHTMARE.
I agree that this seems complicated! But it’s definitely the simplest method I’ve found. And could be very useful to some people 😁
thanks
You're welcome! Thank you for watching and commenting 😀⚡
you are awesome
It's too complicated for me to learn, so I just apply it.😁
😉 How do you use dependent drop-down lists?