If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos. ➡Free Excel Tips Ebook - trumpexcel.com/free-ebook/ Also, I have made all of my Excel courses available for free. You can check these out using the below links: ✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/ ✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course ✅ Free VBA course - bit.ly/excel-vba-course ✅ Free Power Query Course - bit.ly/power-query-course
Really useful! I needed such a solution since ever and created all sorts of workarounds. - This is really smart. Thank you very much. By looking the way you did it, I discovered many other functions I never came across before (Take, Drop). I never finish learning. - Super cool! Thx.
Drop down list only takes those functions that return a range. While TAKE and DROP return a range, FILTER doesn't, so it gives an error when used to make drop down
Nice and insightful 👌🏻 Are there any quick solutions if my data range is expanding, and I don't need to update the formula in data validation every time??
Yeah, for multi-level drop down, the data needs to be sorted, but that can easily be achieved with the new SORT functions in Excel. So even if the raw data is not sorted, you can sort the formula and then use it to make drop downs
I find this method easier. With Indirect, you need to create named ranges which is fine with one level dependent drop down, but get complicated with more levels
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
Great work done mate...
very well explained! And working properly!!
This is such brilliant work! Was searching for this for hours and finally I found your solution! Keep up with the good work!! Thank you so much
Glad you found the video helpful 🙂
If you found this video useful, please give it a thumbs up 👍 and subscribe to the channel. Also, let me know what Excel topics you want me to cover in future videos.
➡Free Excel Tips Ebook - trumpexcel.com/free-ebook/
Also, I have made all of my Excel courses available for free. You can check these out using the below links:
✅ Free Excel Course (Basic to Advanced) - trumpexcel.com/learn-excel/
✅ Free Dashboard Course - bit.ly/free-excel-dashboard-course
✅ Free VBA course - bit.ly/excel-vba-course
✅ Free Power Query Course - bit.ly/power-query-course
This is really helpful Sumit. Thanks a lot for your efforts 🎉
Glad you liked it 🙂
Hey Sumit....This is really useful and I was looking for it. Thanks for creating such a nice video content.
Excellent method Sumit! This saves a lot of time! Thank you for sharing it, Sir!
Excellent. Loved it way you have teach us. Thank you so much Sumit..
Really useful! I needed such a solution since ever and created all sorts of workarounds. - This is really smart. Thank you very much. By looking the way you did it, I discovered many other functions I never came across before (Take, Drop). I never finish learning. - Super cool! Thx.
Glad you found the video helpful 🙂
Thank sir from my heart ❤❤ for your effort
Thank you for the kind words 😊
I haven't listened to this video yet but I'm saving it for later ! I need this so much at work!!!!
Glad you found it useful!
Me too! 🎉
You are next level sir
Great tips, thanks a lot
Thank you so much for explaining this so easily. Mwah!!! The new functions are amazing! 👍🏽👏🏽
For the 2nd part, what about using FILTER function?
Drop down list only takes those functions that return a range. While TAKE and DROP return a range, FILTER doesn't, so it gives an error when used to make drop down
@@trumpexcel Thank you so much...that totally makes sense. 👍🏻
Thanks sumith
Nice and insightful 👌🏻
Are there any quick solutions if my data range is expanding, and I don't need to update the formula in data validation every time??
You can use the TRIMRANGE function when referencing the columns. That way, if the range expands, it would still consider all the filled cells
Does it not work on excel 365 web?
How will it be helpful in the HR department ?
Can I just check, using your formulas, would it mean that the raw data will always have to be sorted in a certain way?
Yeah, for multi-level drop down, the data needs to be sorted, but that can easily be achieved with the new SORT functions in Excel. So even if the raw data is not sorted, you can sort the formula and then use it to make drop downs
Why dont you just use INDIRECT ??
I find this method easier. With Indirect, you need to create named ranges which is fine with one level dependent drop down, but get complicated with more levels