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.
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.
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.
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.
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.
I agree that’s one of best and easiest methods to use, and well explained. Thanks Mark!
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.
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.
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.
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.
again and again, thank you so much for your easier and useful explanation
You’re welcome - I glad you found it useful.
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.
Excellent video presentation.
Very useful.
Thanks Mark.
Thank you. I'm glad you enjoyed it 😁
Very creative. Excellent as always. Thanks Mark👍
Thanks kebin.
Awesome, thanks Mark
Thanks Chris, My pleasure!
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.
wow and peace be upon you sir from me
Thank you 😁
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.
1st comment
Speedy 😀