Thank you so much for your kind words! I'm glad you find my channel helpful for learning new things in Excel. It means a lot to me that you appreciate the work and time I put into creating content.
Thank you so much for your help! I've been trying to use vlookup formula for the last two days but it always showed #ref! or #value! but using this formula worked straight away. Simple example and straight to the point.
Loved the video! But how do I remove/don't show the blanks in the dropdown using this method? I understand how to do it with the filtered data. But if I try this in data validation it says invalid formula
Hi! Loved your video. It helped me out so much. However, I was wondering if there was a way to remove the blanks in the dropdown list using the formula as a source?
Please consider 'building' the base array from a two-column table say Fruit Group, and Fruit Variety....use Unique to generate a unique list of Fruit Group, and a combination of Filter and transpose to generate a range of Fruit variety....then you know the rest....this way the user only maintains a 2-column table...thank you
I can only hope to get an answer but: I am attempting to use the copying method shown @08:36, removing one '$' to move the referred cell down the column, but I keep getting an error. I'm using Microsoft 365, could there be a difference in versions used and displayed?
Same issue, what I did was closed the web version and open it in Desktop Excel, remove the '$' , since it auto-sync to cloud, closed it and open it again in 0365. it works. Cool Video!!! this is the only one that worked for me.
You can use the filter function by filtering out all the non-zero entries. If you do it that way, is more useful to have the zero values than the &"" mentioned on 4:02
I am working on the dependent list and it is exactly what I am looking for!! I have a question about the dependent list "Type". How to remove the empty cell in the drop down list? e.g. the items are only up to row 10, but you include up to row 15 in the range. There are blank selection in the drop down list. How to remove?
7:50 when I enter the dollar signs to reference absolute values, my data validation prompts an error "This entry leads to an error. Try entering different values to continue" . The formula does work without the dollar signs. Any clues?
Great Video - helped me with what I needed. I just subscribed to your channel. I have a question - I skimmed the comments and I don't belive it was asked. At 8:50 you changed the Fruit to Lemons but the Type stayed the same (still listing a Type of orange). Is there a way to "clear" the dependent data validation list (in Type) when you change the "parent" (Fruit) list? Thank you!
Really close to working - but Office 365 doesn't let me remove the one $ sign from the data validation that would let this cascade. So frustrating being so close. Otherwise, this works perfectly.
Great video. However, I am also struggling with extra blank cells. My lookup is in another tab and &"" is not accepted appended to the end of my formula. Would love a video explaining this please.
HELP!!!!!! My values won't work. I need the list to be on a separate excel page. When. I did it, the first value finds the list. The second value that's dependent on the first value finds the list. BUT the third value to be dependent on the second has errors.
having an issue. The dependent works in the First cell I put the formula in, =XLOOKUP(D3,'Category & Sub Master'!C5:J5,'Category & Sub Master'!C6:J16) , When I copy it to the next Cell it copies fine, =XLOOKUP(D4,'Category & Sub Master'!C6:J6,'Category & Sub Master'!C7:J17) , and it brings up the little arrow to allow me to pull down but it will not pull down, the cell will not let me choose anything. Any thoughts on what is happening??
I don't understand how the list in K gets created/appears. I'm following the guide step by step but I keep being met with #VALUE! in your B6 and no extra list being created on the RHS. Hmmm
How about with checkbox clicked. Each checkbox has a value. Example: if the user clicks the Excellent checkbox the value is 1, if Very Good the value is 2 and so forth. So how I can make the value pops up next cell or in another sheet while the user is clicking on options. Later I will count all the values to see how many Excellents, Very Good, and forth.
After typing Xlookup in Data Validation - List and clicking okay im having an error of "This type of reference cannot be used in data validation formula" also adding # is not working please help
HELP!!!!!! My values won't work. I need the list to be on a separate excel page. When. I did it, the first value finds the list. The second value that's dependent on the first value finds the list. BUT the third value to be dependent on the second has errors.
Hello! I had been hunting on the internet for this exact tutorial. Super simple and easy to follow. Thank you so so much!!
Amazing! Super helpful. One of the best channels I stumbled across for learning new things in Excel. Thank you for your work and dedicated time!
Thank you so much for your kind words! I'm glad you find my channel helpful for learning new things in Excel. It means a lot to me that you appreciate the work and time I put into creating content.
Looked at other videos doing Dependent Drop-Down, YOURS is the one that works for me. Thank you so much.
Exactly what I needed in life last night for my project
Thank you so much for your help! I've been trying to use vlookup formula for the last two days but it always showed #ref! or #value! but using this formula worked straight away. Simple example and straight to the point.
This is EXACTLY what I've been looking for! Thank you!
Loved the video! But how do I remove/don't show the blanks in the dropdown using this method? I understand how to do it with the filtered data. But if I try this in data validation it says invalid formula
Thank you so much you are life saver please keep going your brilliant work 😍😍😍😍😊😊😊❤️❤️❤️❤️❤️🌹🌹🌹🌹🌹
Really great tutorial. Helped me a lot on pushing forward my start-up business accounting!
Very Useful & Thanks for the tutorial.
Hi! Loved your video. It helped me out so much. However, I was wondering if there was a way to remove the blanks in the dropdown list using the formula as a source?
Use unique function
Thank you so much for this video. If you Please How to prevent Blank Cell that apeared at the end of drop down list when using XLOOKUP Function
good job Jamie, helped me alot :)
Please consider 'building' the base array from a two-column table say Fruit Group, and Fruit Variety....use Unique to generate a unique list of Fruit Group, and a combination of Filter and transpose to generate a range of Fruit variety....then you know the rest....this way the user only maintains a 2-column table...thank you
Fantastic!!! Thank you. Perfect explanation
Thank you so much for this video. This was exactly what I was looking for!
Many Thanks, The information was of great help !
You're welcome!
Finally, it worked !
I can only hope to get an answer but: I am attempting to use the copying method shown @08:36, removing one '$' to move the referred cell down the column, but I keep getting an error. I'm using Microsoft 365, could there be a difference in versions used and displayed?
Same issue, what I did was closed the web version and open it in Desktop Excel, remove the '$' , since it auto-sync to cloud, closed it and open it again in 0365. it works. Cool Video!!! this is the only one that worked for me.
how would I get the drop down list to omit blank cells without having a empty block? Very helpful tutorial BTW
You can use the filter function by filtering out all the non-zero entries. If you do it that way, is more useful to have the zero values than the &"" mentioned on 4:02
THE BEST!!! Thank you
very perfect Jamie good luck for you go forword
Thank you 😊
Thank you for showing us this tutorial. 👍
I am working on the dependent list and it is exactly what I am looking for!!
I have a question about the dependent list "Type". How to remove the empty cell in the drop down list? e.g. the items are only up to row 10, but you include up to row 15 in the range. There are blank selection in the drop down list. How to remove?
Check out the filter function to filter out empty cells.
@@shahnawazhossenally6059 can you explain?
7:50 when I enter the dollar signs to reference absolute values, my data validation prompts an error "This entry leads to an error. Try entering different values to continue" . The formula does work without the dollar signs. Any clues?
Excellent,
How to remove blank data in dropdown list?
i see in drop down type in your video, there is still blank data.
Thank you ....
Hi thanx for the video, on thing though, can I get rid of the bottom empty row on the 2nd dropdown list?
Great Video - helped me with what I needed. I just subscribed to your channel. I have a question - I skimmed the comments and I don't belive it was asked. At 8:50 you changed the Fruit to Lemons but the Type stayed the same (still listing a Type of orange). Is there a way to "clear" the dependent data validation list (in Type) when you change the "parent" (Fruit) list? Thank you!
Thanks a lot sir🎉🎉🎉❤❤❤😊
Nice, thanks!
It was great thank you!!!
I tried using your tutorial, but the result in the drop down list is always just one result even if there’s multiple ones. Any help please ?
this really helpful, but what happen if we got same value for different criteria (more than 3 criteria).
please show us how can we create 3 level of cascading dynamic drop down list.
at 2:57 , when i click on fx in my excel nothing pops up, i have a mac, is this popup feature not available on mac excel?
Thank you. 🙏🏾🙏🏾🙏🏾
Hi, How to deal if the lookup value in the range is repetitive?
Will you kindly do a tutorial on how to visualize data from excel in power bi
This is perfect! However, Excel won't let me remove the $ signs to be able to copy down the XLOOKUP data validation. Any idea why this is happening?
epic video but where am i going wrong i only can return a single value in the drop down list?
What is the solution to the drop down list blank problem sir xvalucup
What if the amount (result) is dependent on the changes of both Fruit and Type (variables) rather than just Type?
Really close to working - but Office 365 doesn't let me remove the one $ sign from the data validation that would let this cascade. So frustrating being so close. Otherwise, this works perfectly.
Great video. However, I am also struggling with extra blank cells. My lookup is in another tab and &"" is not accepted appended to the end of my formula. Would love a video explaining this please.
Did you find any solution to remove the extra blank cells?
Love this tutorial; Will this also work in Google sheets?
What's the difference between this method and previous one?
This one is a lot quicker, less steps
Hello, is it possible that the Source in my List Data validation doesn't accept a fonction?
HELP!!!!!! My values won't work. I need the list to be on a separate excel page. When. I did it, the first value finds the list. The second value that's dependent on the first value finds the list. BUT the third value to be dependent on the second has errors.
having an issue. The dependent works in the First cell I put the formula in, =XLOOKUP(D3,'Category & Sub Master'!C5:J5,'Category & Sub Master'!C6:J16) , When I copy it to the next Cell it copies fine, =XLOOKUP(D4,'Category & Sub Master'!C6:J6,'Category & Sub Master'!C7:J17) , and it brings up the little arrow to allow me to pull down but it will not pull down, the cell will not let me choose anything. Any thoughts on what is happening??
I don't understand how the list in K gets created/appears. I'm following the guide step by step but I keep being met with #VALUE! in your B6 and no extra list being created on the RHS. Hmmm
How about with checkbox clicked. Each checkbox has a value. Example: if the user clicks the Excellent checkbox the value is 1, if Very Good the value is 2 and so forth. So how I can make the value pops up next cell or in another sheet while the user is clicking on options. Later I will count all the values to see how many Excellents, Very Good, and forth.
Can this be done is Google Sheets?
After typing Xlookup in Data Validation - List and clicking okay im having an error of "This type of reference cannot be used in data validation formula" also adding # is not working please help
BETTER THAN, INDIRECT LOGIC
Thanks
How do I use a xlookup from right to left?
Example: look array - column D .. Return array - column B
Thx
This does not work on online excel :( any suggestions?
We need more like this and also if you can show similar ways in Google sheet as not everyone has Microsoft 365...
it was wowww
May I ask you for email contact? I have difficult filt to solve but I can't do it myself.
I hate how you said you weren't going to use the filter function but end up using it anyway, you only made it more confusing.
WAY TO FAST!🙄
HELP!!!!!! My values won't work. I need the list to be on a separate excel page. When. I did it, the first value finds the list. The second value that's dependent on the first value finds the list. BUT the third value to be dependent on the second has errors.