🆕 Here's a new way to create dependent drop down lists that is much easier! ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-FGOeHbv0L2g.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.
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 ?
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!!!
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
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!
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
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.
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?
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.
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.
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.
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 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, 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.
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
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 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. ✅
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.
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!
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.
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.
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 RU-vid peers of equal calibre. Keep up the great work! Harry
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!
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 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.
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 - 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?
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.
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.
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?
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 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?
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.
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
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!
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 .
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.
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 🙂
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, 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!
