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.
Awesome... It worked... Thank you very much.. I have been going through multiple videos and solutions none ot those worked and especially with =indirect formulas in data validation.. Glad it worked with xlookup formula.. I'm not okay with blanks in few lists but its fine... Thank you
This is a great video and helped a lot. I need to know how you did the conditional formatting. I am also using three columns and all my data is good and working, but how do you do the conditional formatting for stale data? Thanks,
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!
Hi Chandoo, this is an awesome video - you make it clear and easy to follow. I have a question on the CF mentioned in the end though. I watched the video you recommended at the end but am still unclear how to implement the CF to highlight if the options in the drop-downs are mismatched. Grateful for yr advice!
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
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 🙏🏿
Hii.. Love your videos💕 I HAVE A QUESTION in data validation how can I put a custom cell (eg.- If I have 5 products I will put that in a list that I want to validate but I want one extra option like whenever I put "others" I can write in that)....HELP!!!❤
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).
You can't use FILTER formula inside Data Validation rule. So it must be set up outside for every combination. Filter returns an array, while XLOOKUP returns a range. Data Validation formulas need either a physical address or a formula that can return the range.
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.
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
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.
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.
"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))
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
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).
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.
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.
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.
THANK YOU my brother searched every video and each video was very complicated, thanks to you in explaining it in very easy way
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.
This was the best one after referring around 10-12 videos for dependent drop down list. Thanks Man..!!!
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.
😂
Thanks so much - this is the simplest solution I have seen for this problem! Quite a lot easier to implement!
Incredibly Simple , Bro
Awesome... It worked... Thank you very much.. I have been going through multiple videos and solutions none ot those worked and especially with =indirect formulas in data validation.. Glad it worked with xlookup formula.. I'm not okay with blanks in few lists but its fine... Thank you
Thank you ! Could you also provide the worksheet for practice?
This is a great video and helped a lot. I need to know how you did the conditional formatting. I am also using three columns and all my data is good and working, but how do you do the conditional formatting for stale data? Thanks,
Thank you, it was exactly what I was looking for.
You have saved me, my precious Excel Guru!
THANK YOU!
Happy to help!
Thanks a lot, that is what I needed for my dashboard.
Amazing this formula worked. Is there any way that this formula can be worked in Google sheets as well?
You are amazing! I've been trying to do this all day!
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.
Hi Chandoo, this is an awesome video - you make it clear and easy to follow. I have a question on the CF mentioned in the end though. I watched the video you recommended at the end but am still unclear how to implement the CF to highlight if the options in the drop-downs are mismatched. Grateful for yr advice!
Could u share how u automatically highlight row with stale data?
This is brilliant, thanks for sharing!
Glad you enjoyed it!
Than you so much Chandoo. Love from Karachi Pakistan.
chandoo bhai your work is awsome
thanks
Like how we teach our kids A,B,C,D the same way he explains the concepts
Excellent, thanks Chandoo!
Such a cool feature! Thank you.
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
Excellent 🎉.. specially the Pushpa t-shirt..
Thanks for sharing 👍
My pleasure 😊
Thank you Chandu😊
Content is as good as your t-shirt (taggede le😊)
🧔👋
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 😀😀
Nice work, how to link prices of each items after the final selection?
Hii.. Love your videos💕 I HAVE A QUESTION in data validation how can I put a custom cell (eg.- If I have 5 products I will put that in a list that I want to validate but I want one extra option like whenever I put "others" I can write in that)....HELP!!!❤
Your t shirt is very good. Where you have bought??
Excellent chandoo
Hi Chandoo, This is great. Is it possible to do this in google sheets?
Awesome video followed by awesome T Shirt :)
Thanks so much!
Awesome explanation. Thank you
Glad it was helpful!
excellent t-shirt!
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))
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.👍
cool solution, thank you for sharing
it is applicable for the google form , can you share the link , it helpful me
Thanks Chandoo.
Can you make similar with named ranges and using the indirect function??
Where in your other "Conditional Formatting" video do you discuss how to highlight STALE data?
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 😊
new sub here, so cool!!! Thank you
Welcome!
Thanks. What is the latest trick?
Dear Chandoo Sir… can you please make same kind of function idea for Microsoft 2019
Sir,please explain about semantic model
Excellent❤
Amazing
Filter formula is a better way to create dynamic depend drop downs, while using XLookup we have to first arrange our data which is time consuming
You can't use FILTER formula inside Data Validation rule. So it must be set up outside for every combination. Filter returns an array, while XLOOKUP returns a range. Data Validation formulas need either a physical address or a formula that can return the range.
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.
Thank you.. thank you
Fabulous!
Does it applicable to Google sheets as well?
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
Thanks a lot!
Can anyone help me understand on the last point on how to go with the conditional formatting. Couldn't find it out.
Hello, is it possible that the Source in my List Data validation doesn't accept a fonction?
Hi, how to do the same work in Google sheet ? pls help
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
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))
Name range you specified cannot be found, getting this error
Can it e done by hlookup in older versions of excel
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.
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
"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.
I am getting error,"This entry leads to an error.Try entering different values to continue."
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
How to use the conditional formatting? The next video doesn't show
See this for an example - chandoo.org/wp/data-entry-forms-with-conditional-formatting-and-validation/
It's not working for me, my data is in another sheet, I tried the same method step by step and I dont know what's wrong, its not working out.
Can we use Index/Match if xlookup functionality is not available?
Yes, you can.
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).
What if the data is in separate Tab ?
why it's not working to my 2nd row huhu
nice
🎉
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!
ITS NOT WORKING IN MY EXCEL.... SOMETHING I'M DOING WRONG... BUT I DON'T KNOW.
Unable to play the video
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.
But chandoo it's not dynamic
1000 likes🎉🎉🎉
First view
I am getting a warning --> this type of reference cannot be used in data validation formula😢
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.
Your video title should have been "How to create a simple Drop Down List", not "Dependent Drop Down" or any other click bait BS....
Did you even watch the video. It is about dependant drop-downs.
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.