After two days of videos, going between google sheets and excel, FINALLY a video that shows how to apply this to more than two cells. You saved me from more days of suffering and your steps are easy to follow without having to change my list. You are awesome and thank you!
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.
Thank you so much. This is the only method that has worked for me following these instructions. Amazing! I was able to do 3 levels. Very grateful for these great instructions. I had try a different method using indirect and it was not working well.
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,
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!
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 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!
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
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).
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
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 🙏🏿
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.
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
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.
Assalamualaikum If the GPA number is ranked by using the Rank function, if the GPA number is same, it will give Rank 1 for one, and Rank 2 for the other. I want to evaluate the total number in the case of the same GPA number, that is, those who have the same GPA number; In their case, I want to rank by considering the total number.
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.
"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))
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.
After two days of videos, going between google sheets and excel, FINALLY a video that shows how to apply this to more than two cells. You saved me from more days of suffering and your steps are easy to follow without having to change my list. You are awesome and thank you!
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.
Thank you so much. This is the only method that has worked for me following these instructions. Amazing! I was able to do 3 levels. Very grateful for these great instructions. I had try a different method using indirect and it was not working well.
THANK YOU my brother searched every video and each video was very complicated, thanks to you in explaining it in very easy way
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.
😂
This was the best one after referring around 10-12 videos for dependent drop down list. Thanks Man..!!!
Thanks so much - this is the simplest solution I have seen for this problem! Quite a lot easier to implement!
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 ! Could you also provide the worksheet for practice?
Could u share how u automatically highlight row with stale data?
Amazing this formula worked. Is there any way that this formula can be worked in Google sheets as well?
You have saved me, my precious Excel Guru!
THANK YOU!
Happy to help!
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!
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
Incredibly Simple , Bro
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!!! 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
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).
Excellent 🎉.. specially the Pushpa t-shirt..
Thanks for sharing 👍
My pleasure 😊
You are amazing! I've been trying to do this all day!
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.👍
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, it was exactly what I was looking for.
Than you so much Chandoo. Love from Karachi Pakistan.
Thanks a lot, that is what I needed for my dashboard.
chandoo bhai your work is awsome
thanks
Nice work, how to link prices of each items after the final selection?
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
This is brilliant, thanks for sharing!
Glad you enjoyed it!
Can anyone help me understand on the last point on how to go with the conditional formatting. Couldn't find it out.
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 😊
Name range you specified cannot be found, getting this error
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??
Hello, is it possible that the Source in my List Data validation doesn't accept a fonction?
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?
it is applicable for the google form , can you share the link , it helpful me
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.
Does it applicable to Google sheets as well?
Hi, how to do the same work in Google sheet ? pls help
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
Hi Chandoo, This is great. Is it possible to do this in google sheets?
Can it e done by hlookup in older versions of excel
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🥲
Like how we teach our kids A,B,C,D the same way he explains the concepts
Excellent, thanks Chandoo!
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))
Such a cool feature! Thank you.
Can we use Index/Match if xlookup functionality is not available?
Yes, you can.
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/
Content is as good as your t-shirt (taggede le😊)
🧔👋
Awesome explanation. Thank you
Glad it was helpful!
Dear Chandoo Sir… can you please make same kind of function idea for Microsoft 2019
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 ?
Thank you Chandu😊
Sir,please explain about semantic model
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.
Thanks. What is the latest trick?
Awesome video followed by awesome T Shirt :)
Thanks so much!
Assalamualaikum
If the GPA number is ranked by using the Rank function, if the GPA number is same, it will give Rank 1 for one, and Rank 2 for the other.
I want to evaluate the total number in the case of the same GPA number, that is, those who have the same GPA number; In their case, I want to rank by considering the total number.
I am getting error,"This entry leads to an error.Try entering different values to continue."
Excellent chandoo
Where in your other "Conditional Formatting" video
excellent t-shirt!
new sub here, so cool!!! Thank you
Welcome!
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.
cool solution, thank you for sharing
why it's not working to my 2nd row huhu
Excellent❤
"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.
Thank you.. thank you
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).
Fabulous!
Thanks a lot!
Haven't noticed pushpa till the end...😊
Unable to play the video
Amazing
Thanks
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.
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
nice
1000 likes🎉🎉🎉
🎉
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.
First view
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.