Awesome Trick to Get Dependent Drop Downs in Excel (works for multiple rows too)
ฝัง
- เผยแพร่เมื่อ 11 ก.พ. 2024
- Need dependent drop downs or cascading drop-downs in Excel? Use this simple but elegant approach.
This technique relies on the powerful & new XLOOKUP function.
Learn more:
Tutorial with sample workbook - chandoo.org/wp/how-to-create-...
Full Excel Course - chandoo.org/wp/excel-school-p... - วิทยาศาสตร์และเทคโนโลยี
![[TH] VCT Masters Shanghai Playoffs DAY 2 // PRX vs G2 | EDG vs TH](http://i.ytimg.com/vi/T5j2XVcDFs0/mqdefault.jpg)
I've been killing myself for 8 hours and 5 other videos...to the point of tears. I don't know how you pulled it off, but your method worked the first time I tried it. Superlative. Thank you.
You have saved me, my precious Excel Guru!
THANK YOU!
Happy to help!
Thanks so much - this is the simplest solution I have seen for this problem! Quite a lot easier to implement!
You are amazing! I've been trying to do this all day!
Thanks a lot! An explanation in less than 10 minutes, how wonderful 🎉
Incredible as always Chandoo, I didn't realise I needed this in my life until your video. Thank you!
"I didn't realise I needed this in my life until your video" should be my channel's tag line.
😂
Excellent, thanks Chandoo!
Such a cool feature! Thank you.
Thank you ! Could you also provide the worksheet for practice?
Than you so much Chandoo. Love from Karachi Pakistan.
Thanks a lot, that is what I needed for my dashboard.
Thank you so much!!! Could you make a video explaining how to conditional format, in this situation? I didn't understand from the videos you have on conditional formating. Which formula to use in the rule of conditional formating? Because I have to relate the value to a list. I don't know how to do this. And the way yours shows the error, was awesome!
Thanks for the comment. I am in the process of making CF video. Stay tuned.
Hello Chandoo. This is a very helpful video. I am wondering if can we use spin button from the developer tab instead of drop down list. For example: the first spin button will spin category and the second spin button will spin only those products of a category selected by first spin. Thanks
Thank you Chandu😊
This is brilliant, thanks for sharing!
Glad you enjoyed it!
cool solution, thank you for sharing
Excellent chandoo
Awesome explanation. Thank you
Glad it was helpful!
Could u share how u automatically highlight row with stale data?
Excellent❤
Excellent 🎉.. specially the Pushpa t-shirt..
Thanks for sharing 👍
My pleasure 😊
excellent t-shirt!
Awesome video followed by awesome T Shirt :)
Thanks so much!
Thanks Chandoo.
Can you make similar with named ranges and using the indirect function??
Fabulous!
Thanks a lot!
Thank you.. thank you
new sub here, so cool!!! Thank you
Welcome!
Like how we teach our kids A,B,C,D the same way he explains the concepts
Where in your other "Conditional Formatting" video do you discuss how to highlight STALE data?
Can you do this without using xlookup function , are there any possibility to do this same thing using index match function?
You can...
Use this formula -
=INDEX($J$5:$N$15,,MATCH(D5,$J$4:$N$4,0))
Hi Chandoo, This is great. Is it possible to do this in google sheets?
Amazing
Thank you so much, super helpful and so much easier than using the INDIRECT option. Is there a way to remove the blanks though in the drop-down for the second dependent list, that may not have have as many options as the others? (for example, Bars has a larger list than Bites, so how do we prevent the bites list to show so many blanks? I saw in your example that those "zeros' only resulted in one single blank, but for some reason mine results in many blanks). My list keeps defaulting to the blanks so unless you scroll up the user wouldnt see everything included in the dropdown list. Thanks!!
You are welcome :)
Excel should automatically remove all those excess blanks (barring one). I suggest updating Excel or waiting a few months instead of writing complex formulas that remove the blanks.
But if you want to go down that route, you can use OFFSET() with XLOOKUP and calculate how many items are there in your list (with COUNTA).
Thank you for your video. Is there a way to remove the blank rows in the data validation?
You can by using an OFFSET formula instead of XLOOKUP. But probably not worth the hassle.
@@chandoo_ a fair tradeoff. One blank row for the convenience of XLOOKUP embedded in the data validation.👍
Hello, is it possible that the Source in my List Data validation doesn't accept a fonction?
Hey Chandoo, I'm a huge fan. Thank you for your consistently great content. I have a question, can i also use the old "Indirect" function to achieve this? Where in the validation, list, =Indirect(D5). Also the categories would have been made named ranges..Bars, Bites etc? Hope you can help 🙏🏿
You can, but I no longer recommend it (as you need to first create all the names too). If you have Excel 365, use the trick I've demoed in this video.
@chandoo_ Thank you. I Do have 365. So I'll study this vid again...thank you again Uncle Chandoo 😀😀
Your t shirt is very good. Where you have bought??
Thanks
Hi, typed in the data as above on a seperate sheet naming it DATA. Entering formula =xlookup(D5,data!$A$1:$E$1,data!$A$2:$E$12), but error tells me "VALUE NOT AVAILABLE .....". Any help troubleshooting this problem would be appreciate, many thanks
Having a similar problem but in my case it returns a value if i only select 1 row but if i selct them all well i get the value option
Great Video , but how can we extend these to 4 levels or 5 Levels . Can you please let me know @chandoo
The process is same, you just need to layout the data. But I recommend limiting it to 3 levels and changing the data set up for your sanity 😊
Can anyone help me understand on the last point on how to go with the conditional formatting. Couldn't find it out.
Can I get these values from a table like I get using filter command?
I will explain better:
I get a table with the collumns: category; product; product size.
I can choose the category and show only the producs from that category in the next drop dow and in the othe next I only wanna show the sizes of that product. And this can follows how long desired.
As of now, Excel data validation needs data to be present in a physical range or from a formula that can return ranges. So that means you have to use XLOOKUP (or OFFSET). FILTER doesn't return a range. It returns an array, so we can't use that as DV formula.
Does it applicable to Google sheets as well?
Content is as good as your t-shirt (taggede le😊)
🧔👋
Can it e done by hlookup in older versions of excel
Sir,please explain about semantic model
Dear Chandoo Sir… can you please make same kind of function idea for Microsoft 2019
Do you have a video on this that does not use xlookup? So that it can be done w/o excel 365.
The approach is same. Instead of XLOOKUP, use this formula in Data Validation screen.
=INDEX($J$5:$N$15,,MATCH(D5,$J$4:$N$4,0))
Please can you do a video on changing text size of specific text in various cells within a worksheet? Is it possible?
Hello Chandoo please help me out here, I am trying to do this on google sheets, but I keep getting an invalid error response anytime I try entering the formula
Unrelated: I have a SQL question... I hope you can help out ...
Inserting data into ms SQL server, theres an error response saying I can't insert more than 1000 rows...
Please do you have a Syntax to help insert more than 1000 rows? Thanks.
I tried a few other videos and got stuck. Thank ypu for explaining each step so clearly, I now have 3 level drop downs and it was so straightforward!🎉
Glad it helped!
Can we use Index/Match if xlookup functionality is not available?
Yes, you can.
Could you pls make the video for calendar drop down while entering date? If it is made earlier, plz share the link. Thanks
Excel desktop doesn't have the calendar option and they are not easy to implement. I think Microsoft has plans for Excel on the web to show calendar for date fields.
@@chandoo_ yup. I'm using calendar for date fields in web. But I'm looking forward to the app version🥲
Where in your other "Conditional Formatting" video
nice
Thanks Chandoo,
If you expand the table, I don't think it will work.
Or is that even possible?
Already found, give the data a name.
You can easily scale this by applying a dynamic named range idea on top.
@@chandoo_ I have done it and it works great.
Thanks
What if the data is in separate Tab ?
🎉
Haven't noticed pushpa till the end...😊
Please make searchable dependent drop down list
Search is now a default feature of Excel drop downs. You don't need to do anything. Just type in the cell and it filters the list down (works in Excel 365).
"You may not use reference operators (such as unions, intersections and ranges) or array constants for Data Validation criteria"
=N7:INDEX(N7:N35,LOOKUP(1,--(N7:N35""),N7:N35))
Double check your formulas. Type it in a cell and see what it returns.
1000 likes🎉🎉🎉
Unable to play the video
First view
Ok, but what if I don't know what I want? Bars, Bites...
All I know is that I want something with peanut butter, and I don't want to search through all of the categories.
In that case, you can use my 2-level validation drop down trick - chandoo.org/wp/two-level-drop-downs/
@@chandoo_ Awesome, I will check it out. Thanks!
Nice but this can be achieved easily using the Indirect function.
I disagree. Indirect requires setting up either named ranges (which is manual) or sub-optimal formulas.
Agree, and with indirect it can be dynamic. So you can add categories and you don’t need to update formulas. If you look for column numer and then use that. So indirect works better.
We can use indirect formula instead of xlookup😂
You need to have either names or calculate the range address for INDIRECT option. I no longer recommend INDIRECT for this purpose as it is too manual and clunky.
Tried to create this xloopup function in browser but it says this entry leads to an error @chandoo
I am not sure if this concept is available in browser version of Excel.