As I didn't have DROP and CHOOSECOL at the time (about a year ago), I've managed to make 4 level dependent dropdown, with some helper cells and filter arrays. It could be 5, I don't remember, would have to look at the file, but it was a nice challenge. Not sure anyone using the model really appreciates it, but hell, that's the live of a Excel model builder 😄
As I’m sure you’ve experienced, INDIRECT is a pain to manage. The method in this video, takes a bit more skill, but is significantly quicker to setup and maintain.
With INDIRECT you have to create separate ranges for every items and for every combination of items. With this method you don't have to do that. So it should be significantly easier to keep up-to-date.
Simply genius 👏 Thanks bro. I was thinking what if we select shoe type from first list and below it displays a picture of the type of toe and how much qty available and same for colors.
I almost understood why this works! Ha! It does make more sense as a solution than the other methods I've seen. I'm looking forward to part 2 where we can choose any one of the categories and the others bwcome dependent... because sometimes I need a certain color shoe vs a certain style! 😊
Part 2 would be very difficult - maybe impossible. You would need to create different sorted areas for every possible order. Then use an IF inside the Data Validation to select the relevant area depending on which other options are selected. Maybe something for the membership - probably too much for YT.
Very nice Mark. The problem with these solutions is that if you choose a different shoe, it would be nice to blank out the other two cells. I still like this solution though.
By design, a hardcoded value in one cell cannot change a hardcoded value in another cell - so it's unlikely that will ever be possible. Unless of course you sprinkle in a bit of VBA to achieve it for you.
A data validation list only calculates when the button is clicked, it's not part of the main calculation chain. So I believe performance impact should be insignificant.
I believe UNIQUE/SORT/FILTER only works if you output the result to a range. Therefore, it's only suitable with a single chain of dependent lists. If you have multiple rows of cell requiring drop-downs, I don't think it works so well.
Fantastic video! Definitely helped me start understanding more about how the dynamic arrays work and really useful practical application. I was wondering if there's any difference in omitting the "-" part of the XMATCH arguments and just putting the ampersand? ie K6&L6,$F$6#&$G$6#
If your lookup array contains the following: A1 | 1 A | 11 The only way to select the correct value is by using a space character between the values (which is what the "-" does). So, it's a good idea to always include one, just in case.
Thank you! This is an excellent video showcasing a range of new features. However, I'd like to highlight one point: when I tried the formula with # in Data Validation today in Excel 365, I couldn't validate it. After updating my Excel 365, I was able to validate the formula in Data Validation. So, while the process is fantastic, I’ll hold off on distributing data validation with # references until it’s more stable across versions.
The issue is not the #, which is available as far back as Excel 2021. The issue is DROP and TAKE functions, which are in Excel 365 and Excel 2024. With such a large percentage of users on Excel 365, I see less and less reason to state which versions things work on.
Question: To select the color you would have to have already selected the toe? If I wanted to select the color second after the shoe, would I need to redo the order of the dependent formulas?
You could have any order you want - you just need to understand which formula to use for the - Drop-down - Dependent drop-down - Dependent-dependent drop-down You can then keep adding to the formulas and create as many levels as you wish.
"No tricky formula" : proceeds to use formulas I've never heard about with array brackets hahaha The solution was fairly easy to apply though !! Many thanks !!
You're the first person to identify that I said "No tricky formulas" and then proceeded to pull out some reasonably tricky formulas. 10/10 for paying attention. 😁
A cell cannot change the existence of a hardcode value in another cell, so you would need VBA for. You can always use conditional formatting to highlight the items which are wrong.
There are always ways in Excel to do things creatively as you have discovered. It's just too bad that Excel can't allow the FILTER function within a named range formula.
You need a multi level sort to ensure the ranges for the sub levels work. So you need to sort in the Shoe, Toe, then Color. You can achieve this with SORTBY, but the SORT syntax is shorter. If your data is already grouped correctly, you don’t need to sort at all.
When I do the Data Validations, it doesn't give me the unique listing you have, but instead duplicates all the values in the range if the are multiple instances of the value. Any idea why that is?
Really ??? But, if you have 100 possible items, you have to create 100 named ranges. Plus it takes constant maintenance for new items being added. I’m guessing you love manual work 🤣
Thank you so much. I search a lot of method how to do that. This video very useful.
Great news, glad I could help. 😁
Hello, thank you for sharing this cool tip. Btw do you have any solution for Excel version 2019?😢
I agree that’s one of best and easiest methods to use, and well explained. Thanks Mark!
Thanks - I hope you can put it to good use. 👍
As I didn't have DROP and CHOOSECOL at the time (about a year ago), I've managed to make 4 level dependent dropdown, with some helper cells and filter arrays. It could be 5, I don't remember, would have to look at the file, but it was a nice challenge. Not sure anyone using the model really appreciates it, but hell, that's the live of a Excel model builder 😄
Very useful, I used to use "indrect" formula. This look interesting too.
As I’m sure you’ve experienced, INDIRECT is a pain to manage. The method in this video, takes a bit more skill, but is significantly quicker to setup and maintain.
again and again, thank you so much for your easier and useful explanation
You’re welcome - I glad you found it useful.
Excellent video presentation.
Very useful.
Thanks Mark.
Thank you. I'm glad you enjoyed it 😁
Interesting technique, I'll have to compare it to the indirect function I'm using to see if it's more efficient. Thanks for sharing.
With INDIRECT you have to create separate ranges for every items and for every combination of items.
With this method you don't have to do that. So it should be significantly easier to keep up-to-date.
Very creative. Excellent as always. Thanks Mark👍
Thanks kebin.
I sort of followed that through, what a formula you end up with, thanks Paul
If you’ve not see these formulas before, it can be a little tricky.
Simply genius 👏 Thanks bro.
I was thinking what if we select shoe type from first list and below it displays a picture of the type of toe and how much qty available and same for colors.
Sounds like a beautiful solution to me. Great idea.
I almost understood why this works! Ha! It does make more sense as a solution than the other methods I've seen.
I'm looking forward to part 2 where we can choose any one of the categories and the others bwcome dependent... because sometimes I need a certain color shoe vs a certain style! 😊
Part 2 would be very difficult - maybe impossible. You would need to create different sorted areas for every possible order. Then use an IF inside the Data Validation to select the relevant area depending on which other options are selected.
Maybe something for the membership - probably too much for YT.
Very nice Mark. The problem with these solutions is that if you choose a different shoe, it would be nice to blank out the other two cells. I still like this solution though.
By design, a hardcoded value in one cell cannot change a hardcoded value in another cell - so it's unlikely that will ever be possible. Unless of course you sprinkle in a bit of VBA to achieve it for you.
Wow. Excellently explained as usual. I wonder how performance will be affected if using large data sets though
A data validation list only calculates when the button is clicked, it's not part of the main calculation chain. So I believe performance impact should be insignificant.
Thank you for your very good explanation. I like it.
I found it easier to use unique(sort(filter() with the filter referring to the higher level selection.
I believe UNIQUE/SORT/FILTER only works if you output the result to a range. Therefore, it's only suitable with a single chain of dependent lists.
If you have multiple rows of cell requiring drop-downs, I don't think it works so well.
Fantastic video! Definitely helped me start understanding more about how the dynamic arrays work and really useful practical application. I was wondering if there's any difference in omitting the "-" part of the XMATCH arguments and just putting the ampersand? ie K6&L6,$F$6#&$G$6#
If your lookup array contains the following:
A1 | 1
A | 11
The only way to select the correct value is by using a space character between the values (which is what the "-" does). So, it's a good idea to always include one, just in case.
@ExcelOffTheGrid thanks very much!
Great stuff 👌
Thank you! Cheers!
Awesome, thanks Mark
Thanks Chris, My pleasure!
Thank you! This is an excellent video showcasing a range of new features. However, I'd like to highlight one point: when I tried the formula with # in Data Validation today in Excel 365, I couldn't validate it. After updating my Excel 365, I was able to validate the formula in Data Validation. So, while the process is fantastic, I’ll hold off on distributing data validation with # references until it’s more stable across versions.
The issue is not the #, which is available as far back as Excel 2021. The issue is DROP and TAKE functions, which are in Excel 365 and Excel 2024.
With such a large percentage of users on Excel 365, I see less and less reason to state which versions things work on.
Excellent suggestion, Mark. I like it because the INDIRECT function does not appear. Thanks for sharing.
It’s much easier to manage than INDIRECT because we don’t have to add new named ranges for each new item.
I have done dependent drop downs but they always had helpers and were a bit fiddley, really great use of these newer functions.
Hi Mark! Your tips are great; I bet, you're also dreaming in rows and columns ;-)
Question: To select the color you would have to have already selected the toe? If I wanted to select the color second after the shoe, would I need to redo the order of the dependent formulas?
You could have any order you want - you just need to understand which formula to use for the
- Drop-down
- Dependent drop-down
- Dependent-dependent drop-down
You can then keep adding to the formulas and create as many levels as you wish.
"No tricky formula" : proceeds to use formulas I've never heard about with array brackets hahaha
The solution was fairly easy to apply though !! Many thanks !!
You're the first person to identify that I said "No tricky formulas" and then proceeded to pull out some reasonably tricky formulas. 10/10 for paying attention. 😁
wow and peace be upon you sir from me
Thank you 😁
Hi is there a way to user the filter function results on a table?
Great technique. The next step is to reset the dependant list selection if the parent is changed….
A cell cannot change the existence of a hardcode value in another cell, so you would need VBA for.
You can always use conditional formatting to highlight the items which are wrong.
There are always ways in Excel to do things creatively as you have discovered. It's just too bad that Excel can't allow the FILTER function within a named range formula.
Excellent Mark, I used =SORTBY(Data,Data[Shoe]) to sort the table, the other part was amazing.
You need a multi level sort to ensure the ranges for the sub levels work. So you need to sort in the Shoe, Toe, then Color.
You can achieve this with SORTBY, but the SORT syntax is shorter.
If your data is already grouped correctly, you don’t need to sort at all.
@@ExcelOffTheGrid, =SORTBY(Data,Data[Shoe],1,Data[Toe],1,Data[Color],1) 😃
Yes, that will do it.
When I do the Data Validations, it doesn't give me the unique listing you have, but instead duplicates all the values in the range if the are multiple instances of the value. Any idea why that is?
Thanks, I remember there was a total brainf.k way for depended dropdown lists.
Surely FILTER() is much simpler and achieves the same for the dependent lists, or am I missing something?
How are you going to use FILTER,
if you have 3 columns of dependent drop-downs across 100 rows?
1st comment
Speedy 😀
Use indirect formula also much easier than this.
Really ???
But, if you have 100 possible items, you have to create 100 named ranges.
Plus it takes constant maintenance for new items being added.
I’m guessing you love manual work 🤣