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 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.
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
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?
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.
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 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.
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?
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!
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.
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
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.
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.
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?
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??
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!!
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
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
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.
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
Thank you so much you are life saver please keep going your brilliant work 😍😍😍😍😊😊😊❤️❤️❤️❤️❤️🌹🌹🌹🌹🌹
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
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 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.
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
Really great tutorial. Helped me a lot on pushing forward my start-up business accounting!
Fantastic!!! Thank you. Perfect explanation
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 ?
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.
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?
This is EXACTLY what I've been looking for! Thank you!
Nice, thanks!
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?
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!
epic video but where am i going wrong i only can return a single value in the drop down list?
very perfect Jamie good luck for you go forword
Thank you 😊
Very Useful & Thanks for the tutorial.
this really helpful, but what happen if we got same value for different criteria (more than 3 criteria).
Hi thanx for the video, on thing though, can I get rid of the bottom empty row on the 2nd dropdown list?
What if the amount (result) is dependent on the changes of both Fruit and Type (variables) rather than just Type?
Many Thanks, The information was of great help !
You're welcome!
Thank you so much for this video. This was exactly what I was looking for!
Thank you for showing us this tutorial. 👍
Hi, How to deal if the lookup value in the range is repetitive?
Thanks a lot sir🎉🎉🎉❤❤❤😊
Finally, it worked !
please show us how can we create 3 level of cascading dynamic drop down list.
Love this tutorial; Will this also work in Google sheets?
good job Jamie, helped me alot :)
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?
THE BEST!!! Thank you
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
What's the difference between this method and previous one?
This one is a lot quicker, less steps
Will you kindly do a tutorial on how to visualize data from excel in power bi
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, is it possible that the Source in my List Data validation doesn't accept a fonction?
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.
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?
BETTER THAN, INDIRECT LOGIC
It was great thank you!!!
How do I use a xlookup from right to left?
Example: look array - column D .. Return array - column B
Thx
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??
Thank you. 🙏🏾🙏🏾🙏🏾
Thanks
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
This does not work on online excel :( any suggestions?
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?
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.