🆕 Here's a new way to create dependent drop down lists that is much easier! ua-cam.com/video/FGOeHbv0L2g/v-deo.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.
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
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!!!
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
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.
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.
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!
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 ?
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!
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.
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.
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, 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!
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?
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 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 UA-cam peers of equal calibre. Keep up the great work! Harry
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.
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. ✅
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.
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.
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, 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.
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
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?
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?
I was so excited! Then discovered it only is good for 1 single row. I am TRYING to do what seems like a simple thing; first drop down for specialty, select specialty, then go to 2nd dropdown for items only in that chosen specialty, select one item from the 2nd drop down and it returns a price in a 3rd column based on the previous selection. I cannot for the life of me get this to work regardless of the hours, named values, tables, ranges, vlookups, xlookups, match, if, unique and combinations I have tried. Beyond frustrated for something I know cannot be that difficult. Back to the web I go.
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?
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.
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?
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.
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 🙂
Hi Kevin, Thank you very much for your sharing. After I entered Sales Person e.g. Kevin and selected Customer e.g. ALDO, I changed to another Sales Person e.g. Oliver later without re-select from Customer list and ALDO remains in the cell. Is it possible this cell can be refreshed/ be blank automatically or a message to remind user to re-select customer cos it may be a risk to mis-lead or incorrect for further calculation when user forgot to select from the dependent dropdown list? Thank you in advance for your help.
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
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)
Has anyone got this to work on additional rows??? I can only get the dependent picklist to display for whatever us selected in the first cell for the first list. I tried adding the "transpose" piece to the formula and just received a spill error?
Hi Kevin thanks for helpful video and if I have to use same function for every sales person for example if I choose Row 9 for Ava then how can I do this same Drop Down menu for Customer Ava list from. Please help me.
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.
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 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 ....
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 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.
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.
I would like to have same dependent drop down lists on my next rows as well. Pls advise how to copy formulas to in order to have same algorithm in other rows. Thanks in advance.
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 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.
thank you for your video. have been struggling with drop downs. many instruct using the indirect formula. i could never get it to work. I have one question. if I want to use this formula to populate more than one sheet, am I to add any different formulas? it works fine on one page but when I add that same formula on another sheet it doesn't work.
Great video, how do we now have multiple rows where depending on the drop down we have the options shown not just for one single row? :) Not sure how practical it would be to create multiple filtered list to choose from?
As a new subscriber to your channel (and a very old lady), is there any way you can start a series of "How to's" directed at my (over 60) demographic. I learned video editing from you but there's a lot more to learn. Just finished watching your video about leaving Microsoft. You did a wonderful thing to go out on your own. Best of all successes to you!
The information you provide on here is absolutely invaluable. Thank you so much for sharing it with us. I'm curious if you might know if there is a way to create a drop-down list of data based on a corresponding yes/no value? Something like an employee availability list that changes based on whether any of the given names is indicated to be present or absent for the day? Great thanks again for sharing your knowledge!
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 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
Hi, Thanks for a wonderful video! This was desperately required however I am unable to put this validation to the entire column instead of just one cell. Please help.
Great tutorial and I didn't know about these functions. One thing you didn't show to wrap up your example is how to reproduce the formulas in the other cells in your example table. You showed everything only from the perspective of the first cells (A8, B8).
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.
Hi Mate, Thanks for sharing fantastic Video. I am stuck with one thing. How do I use this formula for second row. I am not getting desired result in second drop down column.
Hi Kevin, Love this video! I did it step by step BUT could not figure out how to get the drop down in the "customer" column- I tried so many times! Please help
🆕 Here's a new way to create dependent drop down lists that is much easier! ua-cam.com/video/FGOeHbv0L2g/v-deo.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.
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
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!!!
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 can’t find anyone else who explains this better. So glad I have found 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.
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.
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!
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
?
Best tutorial I have ever watched on UA-cam. Perfectly spoken language!!!! Really really appreciate!!!
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!
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
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.
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, 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!
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 🤷♂️
Is there a way to copy this to multiple rows? I am creating a document that should have the same dropdowns on different rows.
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! 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.
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 UA-cam peers of equal calibre.
Keep up the great work!
Harry
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.
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.
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. ✅
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.
That is brilliant, unbelievable. Even the additional pieces like SORT, UNIQUE. Thank you very much.
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
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
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.
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.
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
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?
How do you replicate this action at a larger scale? Or do you have to do it for each cell one by one?
This is the type of video that I am always interested in watching. Thanks for the great content.
Glad to hear it!
Kevin, you are just a magic human
This was soooo easy - I got it first try and I LOVED that you made it pretty by removing the zeros! Brilliant.
That's amazing I was looking something like this only. It will be helping in our day to day life. THANKS KEVIN.
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?
Thanks Kevin! It was easier than I expected only because you explained everything so well.
How do you do this function on cells below automatically?
Thanks, Kevin. Very easy method. You saved me some pain trying to use a different and more painful method.
I was so excited! Then discovered it only is good for 1 single row. I am TRYING to do what seems like a simple thing; first drop down for specialty, select specialty, then go to 2nd dropdown for items only in that chosen specialty, select one item from the 2nd drop down and it returns a price in a 3rd column based on the previous selection. I cannot for the life of me get this to work regardless of the hours, named values, tables, ranges, vlookups, xlookups, match, if, unique and combinations I have tried. Beyond frustrated for something I know cannot be that difficult. Back to the web I go.
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?
Thank you Kevin. I'm crazy to do that. As I'm a M&E Officer, I needed excel database. Super thank you.
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.
A huge help Kevin. Simplified and refunctioned an Excel assessment tool that I couldn’t make work for years!
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?
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.
impressive. I wish I had learned all that Excel can do prior to my retirement - excellent and thanks Kevin, you are a big help
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!
thank you for your explanation. I have practice what you explain but it doesn't work for multiple rows. can you explain this issue.
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 🙂
You are Awesome, you make it look so simple. Clean and Crisp.
It was great, Kevin. I was struggling with this matter, but with your help, it became a piece of cake. Thank you, man!
So simple and easy to understand the concept of dependant drop down lists
BEST TUTORIAL! I tried so many others and they just didn't work. thank you!
Wow I just received the notification for this when I was thinking if doing multiple lists were even possible
I am from India.
You are awesome and too cool 😎
Hi Kevin, Thank you very much for your sharing.
After I entered Sales Person e.g. Kevin and selected Customer e.g. ALDO, I changed to another Sales Person e.g. Oliver later without re-select from Customer list and ALDO remains in the cell. Is it possible this cell can be refreshed/ be blank automatically or a message to remind user to re-select customer cos it may be a risk to mis-lead or incorrect for further calculation when user forgot to select from the dependent dropdown list?
Thank you in advance for your help.
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
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)
Has anyone got this to work on additional rows??? I can only get the dependent picklist to display for whatever us selected in the first cell for the first list. I tried adding the "transpose" piece to the formula and just received a spill error?
Hello Kevin,
Thank you for the great videos. How can you generate a table of all multiple selections made from the drop down menu.
Hi Kevin thanks for helpful video and if I have to use same function for every sales person for example if I choose Row 9 for Ava then how can I do this same Drop Down menu for Customer Ava list from. Please help me.
Question: Your xlookup filter list is only looking at A8. How do you make the filter look at the next input. Say now you are on A9 or A12?
Thanks for this update. I will surely apply this.
What happens when you need to enter sales person in next row?
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.
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.
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 ....
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.
It was really easier than I thought. Thanks for explaining in easy way
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?! :)
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.
I would like to have same dependent drop down lists on my next rows as well. Pls advise how to copy formulas to in order to have same algorithm in other rows. Thanks in advance.
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! 👍
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.
thank you for your video. have been struggling with drop downs. many instruct using the indirect formula. i could never get it to work. I have one question. if I want to use this formula to populate more than one sheet, am I to add any different formulas? it works fine on one page but when I add that same formula on another sheet it doesn't work.
Great video, how do we now have multiple rows where depending on the drop down we have the options shown not just for one single row? :) Not sure how practical it would be to create multiple filtered list to choose from?
Kevin thanks for the tutorial. So let's say I want to have the drop down selection in 20rows, do I have to repeat the same process for each row??
As a new subscriber to your channel (and a very old lady), is there any way you can start a series of "How to's" directed at my (over 60) demographic. I learned video editing from you but there's a lot more to learn. Just finished watching your video about leaving Microsoft. You did a wonderful thing to go out on your own. Best of all successes to you!
The information you provide on here is absolutely invaluable. Thank you so much for sharing it with us.
I'm curious if you might know if there is a way to create a drop-down list of data based on a corresponding yes/no value? Something like an employee availability list that changes based on whether any of the given names is indicated to be present or absent for the day?
Great thanks again for sharing your knowledge!
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
how do I make this work with subsequent rows after this, will I need a Xlookup function for EVERY row?
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.
Thanks kevin its really helpful but t how can i do it for a full column ?
Wow.. Very useful! .
You packed so much useful information into a simple and fast tutorial.
Much Appreciation.
Great job, Kevin! Do you just copied the formula for the whole column? In my case, it only work for 1 row? Thoughts!
Hi Kevin you're videos are of great help to students and professionals. Can you make a video Microsoft Yammer please. Thanks
Thanks for the suggestion! I've added this to my list. Stay tuned. Cheers!
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
Hi Kevin, its a useful lesson thank you very much, u did this in same sheet, how can we do it in two different sheet information?? plz advise...
Kevin - Brilliant, I producing a compentancy based list, but I need a dependency of a dependency - Would I have two filtered lists?
Hi,
Thanks for a wonderful video! This was desperately required however I am unable to put this validation to the entire column instead of just one cell.
Please help.
Super explanation of dependant drop-down lists
Great tutorial and I didn't know about these functions. One thing you didn't show to wrap up your example is how to reproduce the formulas in the other cells in your example table. You showed everything only from the perspective of the first cells (A8, B8).
Thanks, can we modify it to be searchable dropdown list
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.
Hi Mate, Thanks for sharing fantastic Video. I am stuck with one thing. How do I use this formula for second row. I am not getting desired result in second drop down column.
Hi Kevin, Love this video! I did it step by step BUT could not figure out how to get the drop down in the "customer" column- I tried so many times! Please help
Wow. I tried other videos and failed to learn, this is so amazing explanantion and I understood easily!