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!
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 !!
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
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.
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?
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 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!
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?
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
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.
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!
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!
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!
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. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
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
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.
Absolutely Amazing and Super Clear Explanations. Had it implemented on my use case today ! 👍
I cover similar method with smart indirect. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
I'll be using this to automate my time sheet as I work with several charge numbers under different projects. Thanks!
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. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
Thanks alot
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. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
Is there a way to do this with the "Options" (data) on a separate workbook?
I cover similar method with smart indirect. th-cam.com/video/KyxBV2y-zW8/w-d-xo.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?
This is great! Is there a way to have the last level auto populate?
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. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
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?
Great tutorial...Wish I followed this one first :) Anyway to transpose this ?
Awesome! Thank you.
Fantastic! Very clearly explained.
How can we do this in google sheets?
Terima kasih banyak
Easiest method ever.
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
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. th-cam.com/video/KyxBV2y-zW8/w-d-xo.html
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.
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.
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!
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?