🆕 Here's a new way to create dependent drop down lists that is much easier! th-cam.com/video/FGOeHbv0L2g/w-d-xo.html 🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com ✅ This requires the =xlookup function, which is available as part of Microsoft 365. Unfortunately, older versions of Excel don't currently support this. ✅ To use this on multiple rows, use =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns. ✅ You can access the sample sheet here: 1drv.ms/x/s!AmxrofZZlZ-whMc5Q7m_2f01bKrmyw?e=4vfT8e
I tried to use transpose to copy it down for all my rows, without success. Your sample sheet has no drop down lists for the Customer column B so I cannot see it in use. Please post a follow up video showing how to do use this for subsequent rows. Thanks in advance.
This by far was the easiest way I have seen to use an 'X' lookup. I have been intimidated by the lookup functions, but by watching your tutorial, I am more confident in using this going forward. Thank you!!!
The XLOOKUP formula is referencing the top row of your 1st column to return the "Filtered List". But when you go list the customers for the next sales rep on the 2nd row, it still references the first sales since that is the "Lookup Value" in the XLOOKUP formula
@@rishabhkanwar4896 In the xlookup most likely you didn't select the right cell. In the video he selected A8 to select the name Kevin. Probably you selected another cell in the worksheet where you have entered the name Kevin, and that will not work. You must select the cell where you actually pick the name Kevin or any other values in the dropdown list.
Thank you so so much for this. I have been trying to create a dependent drop down for about a week using 8 different tutorial videos, some of which had so many steps I gave up half way. Your video was so quick and easy to follow that I was able to do the steps in real time alongside you. My spreadsheet is finally complete, looks great and working beautifully. No changing cell names, lots of gaps or error codes for having spaces. I could hug you
I had the same problem but I think I just found a solution. In my data validation settings, I added an IF formula to the Xlookup referenced cell and it seems to work for me
I am so amazed how much you know about Microsoft. I know you worked there, but your videos have made look great at my company. I have built some awesome files because of your videos. Thank you very much.
Select the cell on which you have just added data validation. Drag and select all the rows on which you wish to add the same data validation (make sure the cell on which you added data validation is selected). Now just press Ctrl+D And done! Just delete the value which appears in the cells below.
Thank you so much for this video! This was easier than what I've seen in other videos! I love that you provided instructions for adding this to multiple rows. Would you ever consider making a video about it? I followed the entire video and succeeded until I tried adding to multiple rows. THANK YOU!
Hi Kevin. Glad to see that Kevin Cookie Company is doing well! How do you apply these steps to the subsequent rows because the example you are showing is only on the first row? Thank you in advance.
Thank you Kevin! Your videos are always helpful, I learn something new every time. Also appreciate that you keep them relatively short, pretty high level and with examples that help me apply the functionality to my everyday job role. Great information as usual!
Thank you, thank you, thank you for this video! I have spent hours trying different methods to get a dependant drop down list and none of them worked until this video! This is saving me so much time in work and now we have a feasible way to sub categorise data without having to type it all! Thank you!
Explained so well and kept to the point perfectly. Not like other excel trainings that drag on and on. It was exactly what I was looking for and it worked perfectly the first time.
Is it me? The tutorial was great but it only works for the first cell, as there is an Xlookup off that cell. So when I move to row two the solution wont work, which seriously limits its usability
I have to do this for 600 rows, and making a filtered list 600 times doesn’t seem plausible , there has to be an easier way. Any ideas @KevinStratvert ?
How would you do this if you want to have multiple lists for multiple sales persons showing at once? Like if you want both Oliver and Kevin to be listed in A8 and A9. How would you have a tailored dropdown for each?
I watched 8 different videos, with three different ways of creating Dependent Drop Down Lists. Your's works and the others either didn't work or were too hard to understand! Thanks for making my evening. ✅
Thank you Kevin! Top notch as always! Question: is there a way to prompt a value in the cell when you just type its initial letter? Let’s suppose you have a long drop down list to go through many times this would be very helpful. Thanks
Hi there Kevin, you made it very easy for me, I typed (searched) just the exact same search on youtube and watched other "helpers" but your´s was the best. Thanks a lot. Nice job bud. Kind Regards.
This is an easier way of doing it. I used to do OFFSET for the dependent drop-down list (I learned it from Leila), which does the job but can be a bit tricky. I'll try and integrate the SEARCH function with this so the drop-down would be searchable. Thank you Kevin! 😘
Yeah, this is by far the easiest way to pull this off. The formulas actually ends up being pretty concise and easy to understand too. Now only if they would allow you to put an xlookup directly into data validation, then you wouldn't need a filtered list on the sheet. I guess that's a feature request.
Great video, but can you also add on to this to have multiple rows? With the Dynamic Formulas we are only able to create one row after that the Spill Error can occur.
Hi Kevin, I thought I would drop a quick comment on your presentation of dependant drop-down lists. I found your explanations and step by step instructions, very concise, accurate and clear. It is instructional videos such as these that really allow your viewers / subscribers to become very productive not only with XL and other Office mainstream apps, but also with other products (hardware or software) that you demo. I would suggest that you have only 3 or 4 TH-cam peers of equal calibre. Keep up the great work! Harry
Clear and concise. Great learning and teaching moment. Thanks again Kevin. Your video tutorials are the best. Subscribing ti your channel is one of my best decicions.
did he replied? seems like it didnt work when i copied the first drop down. and when I selected another drop down it just showed the independent drop down of the first one
Great video Kevin. Quick question: I noticed that you made the validation list work for row number 8, but when I'm working on any other row and pick a different name I still keep getting the same list as if I was in row 8. How can I get the correct list of items associated with other names in all the other rows? I hope that what I'm saying makes sense. Thank you for your time.
If I’m understanding your question correctly, you should note that he set the data validation only for one cell (A8). The cell directly below it has no data validation set. Did that hit on your problem?
Instead of entering the reference to the XLOOKUP array (=$H$8#), copy and paste the formula =XLOOKUP(A8,$D$7:$F$7,$D$8:$F$19) into data validation. Then you can copy the cell down the column. The drawback is you're not able to use UNIQUE or SORT with it.
@@eugene5910 I do not understand your reply. Can you give more details in the copy and paste into data validation. I still get errors when I attempt doing what you suggested.
@@rogerwalters8899 If you want the dropdowns for multiple rows, bypass creating the filtered list array for the dependent "Customer" field and enter the XLOOKUP function directly into data validation as the source. Also, Kevin addresses the multiple row issue by using the TRANSPOSE function in his description. You will have a filtered list for each row, but you can hide or keep the list in another sheet.
Hey Kevin, thanks for the tip. I'm pretty sure you could have given a name to the range D8:D16 that matches with a member of KCC list. So, in A8 you pick up a name in the dropdown list. Then, in B8, use this formula to validate the list : INDIRECT(A8). When you select "Kevin" in A8, the dropdown list in B8 should be filled automatically with cells D8 to D16 contents.
The real problem with this video is this solution is not dynamic. You can do all this making it dynamic so you don’t have to update your functions every time you update the dataset.
HI Kevin - Thanks. The video was informative and easy to understand. Can you also let me know how do i drag the formula to next row? If in cell A9 Ichoose 'Ava' how do i get the customers for 'Ava' in cell B9?
That's a great tip for me, thanks! I'm preparing a language test for employees of different teams, so i wanted to make this dependent double selection option - other videos i tried to open show VBA solutions which are to advanced for me as I don't do VBA at all. So it's a great simple solution that works for the less advanced too 🙂
yeeeesss to advanced Excel videos!! Sure, I have to search for the translated function name for my language from time to time, but that's worth it. Give me more, pretty please .
I think there is a problem. Please correct me. If you enter "Kevin" in A8 and "Oliver" in A9, because you are fetching from the same array, the list in the customer column is the same, right?
Were you able to find a solution to this issue? I'm trying to create an order form that returns a list of color options based on which product is selected. But if they would like to purchase multiple items I need the color options to update base upon multiple rows of products. This got me closer but not quite there and I'm racking my brain on how to solve the issue.
Great Tutorial. I'm not an IT guy, but I have create a way to have IT Managers give me information that I can then present to the Bosses to review, before I enter the data into an online system with close to 300 data points. This will help me collect and present it in a simple way for the viewer (less simple for me, but hey, that's ok)
HI Kevin, and thanks for the video. But this works only for a single line ( Sales Person - Customer). In fact if I go under the KEVIN > Customer and I select OLIVER under KEVIN, the Customer dropdown shows exactly the same values because Xlookup is linked to a single cell, and the dropdown is linked to the filtered list. How can I use it to have multiple lines of Sales persons each one with a different customer dropdown list ? Thanks for accepting the challenge.
This was very helpful! Now, do you have a video on how to make the spreadsheet only show the dropdown options we've created and not the lists used to create the dropdowns?
This was brilliant! I have seen several ways to create dependent drop downs and this was by far the easiest. Thank you for your superb tutorial it really helped me with a data collection issue I was facing
Really good video, thanks for the vibrant colors! I'll have a full happy lunch today. 1:25 I laughed so hard for some reason - cevin hahaha😂😂. And I repeat again and again, your talking a lot about cookies give me one! Totally forgot about the thumbnail it's a stunner! To see more videos like this never get's old! "HI Everyone Kevin Here", A legend is born! We are marching towards 900K!
Atleast I now know what happened to Adele, she had promised me the recipe🤣🤣🤣🤣. Great content Kevin, not only sre you concise, you waste no time in getting to the point which makes the videos relatively short. Thank You
Hi Kevin, great video! And how do you make a whole table with each line having sellers in the first column and a filtered list of companies on the second? Thanks!
Thanks for the great video Kevin! Is there a way to scale this easily so that say a person could use dependent dropdowns for over a hundred rows that all operate the same way independently? When I try to create a second row using this method it refers back to the filtered lists already generated in the first row and populates the same results. It would be great to do this in the next row and thereafter.
I am surprised that some techniques that are in Microsoft Access can be done in Microsoft Excel, too. I love to arrange my data neatly. Creating multiple dependent drop-down lists in Excel is good for users who do not have Microsoft Access in their computers. Creating a lookup list in Microsoft Excel is a helpful technique, while it is opened. I relish the fact that data management can make sorting rows easier to do in Excel as compared with Access. As a keyboard specialist, I am receptive to learn something new. I enjoy listening and taking your advice.
Thanks Kevin, this was really helpful! However, is there a way for the formula to run in the whole of column A? So for example, if you select cell A10 - the formula won't drag down. How do you fix this?
Very helpful video. But I got into a situation my users are changing the first list/parent value after choosing the dependent list value. Which is making my data collection wrong. How can we change the dependent list value blank, if they change parent value?
Hello Kevin. 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 Salesperson and the second spin button will spin only those customers of a salesperson selected by first spin. Thanks
Hey Kevin, How to use the dependent drop down through out the rows, for instance how to use the same in the second row...What you have showed in the first row...Do we need to make a seperate filtered list for the second row....And so on...
My bad, I should have included that in the video! Insert =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
Thanks for this amazing video. I had a query though. How do paste a same dependant drop down to the other cells in subsequent rows? In your example how do we paste the similar drop down for B10, 11 ....
So far the Best and the easiest Dependent Drop Down formula. Please also suggest how the "Indirect" Formula works, as the version of Excel I am using has the formula, however it never works.
My bad, I should have included that in the video! Insert =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
🆕 Here's a new way to create dependent drop down lists that is much easier! th-cam.com/video/FGOeHbv0L2g/w-d-xo.html
🏫 Learn the fundamentals of Excel in just 2 hours: kevinstratvert.thinkific.com
✅ This requires the =xlookup function, which is available as part of Microsoft 365. Unfortunately, older versions of Excel don't currently support this.
✅ To use this on multiple rows, use =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
✅ You can access the sample sheet here: 1drv.ms/x/s!AmxrofZZlZ-whMc5Q7m_2f01bKrmyw?e=4vfT8e
It would have been great to show how to do the multiple row steps (2 tick)
I tried to use transpose to copy it down for all my rows, without success.
Your sample sheet has no drop down lists for the Customer column B so I cannot see it in use.
Please post a follow up video showing how to do use this for subsequent rows.
Thanks in advance.
How to get rid of this function if later I find out I don’t need it. I can’t get rid of drop down list
@@musicisfunpart click on the cell with the drop down, go back to data validation, then set it to allow all values.
@@KevinStratvert Thanks so much. Love your videos. It is very helpful.
This by far was the easiest way I have seen to use an 'X' lookup. I have been intimidated by the lookup functions, but by watching your tutorial, I am more confident in using this going forward. Thank you!!!
The XLOOKUP formula is referencing the top row of your 1st column to return the "Filtered List". But when you go list the customers for the next sales rep on the 2nd row, it still references the first sales since that is the "Lookup Value" in the XLOOKUP formula
yeah how to get around this???
@@rishabhkanwar4896 In the xlookup most likely you didn't select the right cell. In the video he selected A8 to select the name Kevin. Probably you selected another cell in the worksheet where you have entered the name Kevin, and that will not work. You must select the cell where you actually pick the name Kevin or any other values in the dropdown list.
Its not working for n number of coloums
@@Mezamoe Yeah, but what if you have hundreds of cells?
I'm having the same problem here. Please help.
Thank you so so much for this. I have been trying to create a dependent drop down for about a week using 8 different tutorial videos, some of which had so many steps I gave up half way. Your video was so quick and easy to follow that I was able to do the steps in real time alongside you. My spreadsheet is finally complete, looks great and working beautifully. No changing cell names, lots of gaps or error codes for having spaces. I could hug you
Can we all agree that Kevin's Voice is sooooooooooo calm.
Not a question to me
Yeeessss!
Agreed. He should do audiobooks.
Yes! Trust me, I sound like Bobbi Fleckman and people cringe when they hear me! Kevin should be doing voiceovers!!!!!!!!
I had the same problem but I think I just found a solution. In my data validation settings, I added an IF formula to the Xlookup referenced cell and it seems to work for me
I can’t find anyone else who explains this better. So glad I have found you 😺😺
I am so amazed how much you know about Microsoft. I know you worked there, but your videos have made look great at my company. I have built some awesome files because of your videos. Thank you very much.
Hi Kevin. How do you apply these steps to the subsequent rows because the example you are showing is only on the first row?
same issue
Copy the formula. Cick on the cell you want to copy to, right click, paste special, select "validation".
This should help.
This is the link for even easier way to do it .. enjoy !! It worked for me amazingly
Select the cell on which you have just added data validation. Drag and select all the rows on which you wish to add the same data validation (make sure the cell on which you added data validation is selected). Now just press Ctrl+D
And done! Just delete the value which appears in the cells below.
Found the solution. Use "Transpose" formula. Details in the description of the video.
Thank you so much for this video! This was easier than what I've seen in other videos! I love that you provided instructions for adding this to multiple rows. Would you ever consider making a video about it? I followed the entire video and succeeded until I tried adding to multiple rows. THANK YOU!
Hi Kevin. Glad to see that Kevin Cookie Company is doing well! How do you apply these steps to the subsequent rows because the example you are showing is only on the first row? Thank you in advance.
Hello. Did you learn how to apply it to multiple rows? If yes, Please inform me.
I am unable to copy to subsequent rows
Same here. I am looking to create a tracker with a dependent drop-down list and cannot copy down this formula and make it work. Please help!
I got a semi-solution to this problem. Please, read the comment I put in the video... maybe you could help to transform it in a full solution :D
Thank you Kevin! Your videos are always helpful, I learn something new every time. Also appreciate that you keep them relatively short, pretty high level and with examples that help me apply the functionality to my everyday job role. Great information as usual!
My pleasure!
Thank you, thank you, thank you for this video! I have spent hours trying different methods to get a dependant drop down list and none of them worked until this video! This is saving me so much time in work and now we have a feasible way to sub categorise data without having to type it all! Thank you!
Best tutorial I have ever watched on TH-cam. Perfectly spoken language!!!! Really really appreciate!!!
Explained so well and kept to the point perfectly. Not like other excel trainings that drag on and on. It was exactly what I was looking for and it worked perfectly the first time.
Is it me? The tutorial was great but it only works for the first cell, as there is an Xlookup off that cell. So when I move to row two the solution wont work, which seriously limits its usability
same here. spent 2 hours before looking at this comment
Same here guys!! Did you find a way to work in all the below cells?
I copied created X amount of filtered columns for X amount of rows 😭
I have to do this for 600 rows, and making a filtered list 600 times doesn’t seem plausible , there has to be an easier way. Any ideas @KevinStratvert
?
I watched WAYY too many helpless videos before I found this gem! You're the man! SUBSCRIBED!
How would you do this if you want to have multiple lists for multiple sales persons showing at once? Like if you want both Oliver and Kevin to be listed in A8 and A9. How would you have a tailored dropdown for each?
Simply copy paste the b8 cell to b9 , that's it
@@Mrlame-wx1pf but that still gives you the options that are shown in the filtered list, it doesn't change 🤷♂️
I watched 8 different videos, with three different ways of creating Dependent Drop Down Lists. Your's works and the others either didn't work or were too hard to understand! Thanks for making my evening. ✅
Thank you Kevin! Top notch as always!
Question: is there a way to prompt a value in the cell when you just type its initial letter? Let’s suppose you have a long drop down list to go through many times this would be very helpful. Thanks
Look at his own comments under the video.
Kevin, you are just a magic human
That's amazing I was looking something like this only. It will be helping in our day to day life. THANKS KEVIN.
You are a genius. I watched so so many video where v v v completed steps explained. Yours is simple and awesome!!! You got a subscriber!
Your videos are so well structured and well explained, it removes the issues I didn't even know I'm going to have. Bravo, thanks!
Thank you Kevin. I'm crazy to do that. As I'm a M&E Officer, I needed excel database. Super thank you.
I was about to build a powerApp that could took like two hours. now I built the form in 20 Minutes. Thank you for the tutorial
That is brilliant, unbelievable. Even the additional pieces like SORT, UNIQUE. Thank you very much.
Hi there Kevin, you made it very easy for me, I typed (searched) just the exact same search on youtube and watched other "helpers" but your´s was the best. Thanks a lot. Nice job bud. Kind Regards.
I was thrown into a project, not well versed in Excel. Your tutorials are saving me! THANK YOU!
Great video Kevin. This Multiple Dependent Drop-Down Lists in Excel is more helpful and easy to complete the work. Thanks for pulling out this video.
This is an easier way of doing it. I used to do OFFSET for the dependent drop-down list (I learned it from Leila), which does the job but can be a bit tricky. I'll try and integrate the SEARCH function with this so the drop-down would be searchable. Thank you Kevin! 😘
Yeah, this is by far the easiest way to pull this off. The formulas actually ends up being pretty concise and easy to understand too. Now only if they would allow you to put an xlookup directly into data validation, then you wouldn't need a filtered list on the sheet. I guess that's a feature request.
Thanks!
Wow, thanks so much! Much appreciated 🙏
You are Awesome, you make it look so simple. Clean and Crisp.
Great video, but can you also add on to this to have multiple rows?
With the Dynamic Formulas we are only able to create one row after that the Spill Error can occur.
Indeed, Kevin's solution doesn't work with multiple rows as the dynamic array pointing to same data.
@@jasontan4730Tan Any Idea on how to resolve this.
Thanks
Hi Kevin, I thought I would drop a quick comment on your presentation of dependant drop-down lists. I found your explanations and step by step instructions, very concise, accurate and clear. It is instructional videos such as these that really allow your viewers / subscribers to become very productive not only with XL and other Office mainstream apps, but also with other products (hardware or software) that you demo.
I would suggest that you have only 3 or 4 TH-cam peers of equal calibre.
Keep up the great work!
Harry
Thanks Kevin! It was easier than I expected only because you explained everything so well.
BEST TUTORIAL! I tried so many others and they just didn't work. thank you!
It was great, Kevin. I was struggling with this matter, but with your help, it became a piece of cake. Thank you, man!
impressive. I wish I had learned all that Excel can do prior to my retirement - excellent and thanks Kevin, you are a big help
Clear and concise.
Great learning and teaching moment.
Thanks again Kevin.
Your video tutorials are the best. Subscribing ti your channel is one of my best decicions.
A huge help Kevin. Simplified and refunctioned an Excel assessment tool that I couldn’t make work for years!
Nice tutorial! But how can we apply it to all rows? Please advise. Thank you in advance :)
did he replied? seems like it didnt work when i copied the first drop down. and when I selected another drop down it just showed the independent drop down of the first one
I'm also waiting for this reply. Seems it only working for the first row Only. @Kevin any thoughts
Try restarting at 2:25. I think you are missing the filtered list step.
Still not able to… any help?
Any reply on how to apply it for all rows
This was soooo easy - I got it first try and I LOVED that you made it pretty by removing the zeros! Brilliant.
Very helpful! This video is to the point and one of the most helpful I have seen on TH-cam! Thanks!
Great video Kevin. Quick question: I noticed that you made the validation list work for row number 8, but when I'm working on any other row and pick a different name I still keep getting the same list as if I was in row 8. How can I get the correct list of items associated with other names in all the other rows? I hope that what I'm saying makes sense. Thank you for your time.
Hi Santana, Were you able to get the answer to your question? I am also stuck at the same point. Looking for answer. Thanks in advance
If I’m understanding your question correctly, you should note that he set the data validation only for one cell (A8). The cell directly below it has no data validation set. Did that hit on your problem?
Instead of entering the reference to the XLOOKUP array (=$H$8#), copy and paste the formula =XLOOKUP(A8,$D$7:$F$7,$D$8:$F$19) into data validation. Then you can copy the cell down the column. The drawback is you're not able to use UNIQUE or SORT with it.
@@eugene5910 I do not understand your reply. Can you give more details in the copy and paste into data validation. I still get errors when I attempt doing what you suggested.
@@rogerwalters8899 If you want the dropdowns for multiple rows, bypass creating the filtered list array for the dependent "Customer" field and enter the XLOOKUP function directly into data validation as the source. Also, Kevin addresses the multiple row issue by using the TRANSPOSE function in his description. You will have a filtered list for each row, but you can hide or keep the list in another sheet.
Wow.. Very useful! .
You packed so much useful information into a simple and fast tutorial.
Much Appreciation.
Hey Kevin, thanks for the tip. I'm pretty sure you could have given a name to the range D8:D16 that matches with a member of KCC list. So, in A8 you pick up a name in the dropdown list. Then, in B8, use this formula to validate the list : INDIRECT(A8). When you select "Kevin" in A8, the dropdown list in B8 should be filled automatically with cells D8 to D16 contents.
The real problem with this video is this solution is not dynamic. You can do all this making it dynamic so you don’t have to update your functions every time you update the dataset.
How to expand the rule to A9 as well? When I select another person in A9, I see the same dependent list for Kevin
HI Kevin - Thanks. The video was informative and easy to understand. Can you also let me know how do i drag the formula to next row? If in cell A9 Ichoose 'Ava' how do i get the customers for 'Ava' in cell B9?
Thanks, Kevin. Very easy method. You saved me some pain trying to use a different and more painful method.
That's a great tip for me, thanks! I'm preparing a language test for employees of different teams, so i wanted to make this dependent double selection option - other videos i tried to open show VBA solutions which are to advanced for me as I don't do VBA at all. So it's a great simple solution that works for the less advanced too 🙂
Is there a way to copy this to multiple rows? I am creating a document that should have the same dropdowns on different rows.
This is the type of video that I am always interested in watching. Thanks for the great content.
Glad to hear it!
yeeeesss to advanced Excel videos!! Sure, I have to search for the translated function name for my language from time to time, but that's worth it. Give me more, pretty please .
More to come! 👍
Your channel is the BEST!
Congratulations subscribersss🎉🎉🎉🎉🎉🎉🎉🎉🎉🎉😍
Excellent!! Thanks as Always Kevin!
I am from India.
You are awesome and too cool 😎
I think there is a problem. Please correct me. If you enter "Kevin" in A8 and "Oliver" in A9, because you are fetching from the same array, the list in the customer column is the same, right?
Totally agree that is the first issue i thought of.
@@alex626ification I ended up using =INDIRECT(SUBSTITUTE(B5," ","_")) with a set of "names"
@@kenigiri Did it work out?
Just lock the cell while using xlookup, it won't give u any sort of error or incorrect value
Were you able to find a solution to this issue? I'm trying to create an order form that returns a list of color options based on which product is selected. But if they would like to purchase multiple items I need the color options to update base upon multiple rows of products. This got me closer but not quite there and I'm racking my brain on how to solve the issue.
Great Tutorial. I'm not an IT guy, but I have create a way to have IT Managers give me information that I can then present to the Bosses to review, before I enter the data into an online system with close to 300 data points. This will help me collect and present it in a simple way for the viewer (less simple for me, but hey, that's ok)
HI Kevin, and thanks for the video. But this works only for a single line ( Sales Person - Customer). In fact if I go under the KEVIN > Customer and I select OLIVER under KEVIN, the Customer dropdown shows exactly the same values because Xlookup is linked to a single cell, and the dropdown is linked to the filtered list.
How can I use it to have multiple lines of Sales persons each one with a different customer dropdown list ?
Thanks for accepting the challenge.
Pretty sure his video left out the most important piece... exactly what you stated above. Sad.
This was very helpful! Now, do you have a video on how to make the spreadsheet only show the dropdown options we've created and not the lists used to create the dropdowns?
It was really easier than I thought. Thanks for explaining in easy way
I learn so much from your videos. Your channel is always my go to if I need to figure out how to do something new in M365.
This was brilliant! I have seen several ways to create dependent drop downs and this was by far the easiest. Thank you for your superb tutorial it really helped me with a data collection issue I was facing
Really good video, thanks for the vibrant colors! I'll have a full happy lunch today. 1:25 I laughed so hard for some reason - cevin hahaha😂😂. And I repeat again and again, your talking a lot about cookies give me one! Totally forgot about the thumbnail it's a stunner! To see more videos like this never get's old! "HI Everyone Kevin Here", A legend is born! We are marching towards 900K!
Super explanation of dependant drop-down lists
IT WAS A GREAT HELP REALLY LIKE THE WAY YOU HIGHLIGHT WHEN TEACHING!
Kevin you are the best!
Atleast I now know what happened to Adele, she had promised me the recipe🤣🤣🤣🤣. Great content Kevin, not only sre you concise, you waste no time in getting to the point which makes the videos relatively short. Thank You
This was superb, best Excel training I've ever had!
Thanks, Kevin for your clear presentation. I learn a lot from here.
Hi Kevin, great video! And how do you make a whole table with each line having sellers in the first column and a filtered list of companies on the second? Thanks!
Second to this question. Do we need to have a filtered list for each following line?
Third to this question!
Fourth. I am completely lost on how this is useful/scalable to add more rows without making filtered lists for each row.
You are really genius Kevin..
Thanks for the great video Kevin! Is there a way to scale this easily so that say a person could use dependent dropdowns for over a hundred rows that all operate the same way independently?
When I try to create a second row using this method it refers back to the filtered lists already generated in the first row and populates the same results. It would be great to do this in the next row and thereafter.
Came here with this exact question. Did you ever figure it out?! :)
I am surprised that some techniques that are in Microsoft Access can be done in Microsoft Excel, too. I love to arrange my data neatly. Creating multiple dependent drop-down lists in Excel is good for users who do not have Microsoft Access in their computers. Creating a lookup list in Microsoft Excel is a helpful technique, while it is opened. I relish the fact that data management can make sorting rows easier to do in Excel as compared with Access. As a keyboard specialist, I am receptive to learn something new. I enjoy listening and taking your advice.
Thanks Kevin, this was really helpful! However, is there a way for the formula to run in the whole of column A? So for example, if you select cell A10 - the formula won't drag down. How do you fix this?
Very helpful video. But I got into a situation my users are changing the first list/parent value after choosing the dependent list value. Which is making my data collection wrong. How can we change the dependent list value blank, if they change parent value?
Wow, Thanks, buddy! Very helpful. Learned a lot.
So simple and easy to understand the concept of dependant drop down lists
Excellent Video - Easy to follow and worked like a charm
Hello Kevin. 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 Salesperson and the second spin button will spin only those customers of a salesperson selected by first spin. Thanks
Hey Kevin, How to use the dependent drop down through out the rows, for instance how to use the same in the second row...What you have showed in the first row...Do we need to make a seperate filtered list for the second row....And so on...
Same issue, 2nd row return error..
My bad, I should have included that in the video! Insert =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
@@KevinStratvert it worked, Thanks! Keep up all the great work Kevin.
@@KevinStratvertnot working for me..the 2nd row (till the end of the DB) is depending on the value on the 1st row.
@@alono2323 That's the same result I get.
Congrats Kevin on 800K subsribers 🎉 now let's march toward 1 million
Thanks Kevin. This is what i am looking for. Good explanation and Easy to understand.
Thanks for this amazing video. I had a query though. How do paste a same dependant drop down to the other cells in subsequent rows? In your example how do we paste the similar drop down for B10, 11 ....
Wonderful. I used to use the Indirect function with data validation to achieve this. This is so much better. Thanks
Thanks!! looked through so many complicated videos, yours was super easy and highly effective !
thank you for your explanation. I have practice what you explain but it doesn't work for multiple rows. can you explain this issue.
Yes, Kevin. It was easier than I thought it would be. Great work. Congrats!
Thanks Kevin - this is exactly the tool(s) I was searching for. Clear explanation. Now to find these menus on the Mac.
So far the Best and the easiest Dependent Drop Down formula. Please also suggest how the "Indirect" Formula works, as the version of Excel I am using has the formula, however it never works.
Wow I just received the notification for this when I was thinking if doing multiple lists were even possible
Wow. I tried other videos and failed to learn, this is so amazing explanantion and I understood easily!
Hi
This is great but will it work for Cell A9 and B9 as Xlookup hard coded to Cell A8.
Thanks in advance.
My bad, I should have included that in the video! Insert =transpose as part of the formula: =TRANSPOSE(SORT(UNIQUE(XLOOKUP(A8,Table1[#Headers],Table1),,TRUE))) Then you'll just need to copy the formula down for however many rows you have. You can even place the filtered list on a separate sheet or just hide those columns.
Kevin - thank you - another amazing excel tutorial.
Excellent Work Kevin
Excellent explanation brother.... Simply Excellent 👍👍
How do you replicate this action at a larger scale? Or do you have to do it for each cell one by one?
Thank you Kevin! 😘😘😘
I love how easy you describe each action. Thanks!