🆕 Here's a new way to create dependent drop down lists that is much easier! kzitem.info/news/bejne/p32Fy3yYr2J9ZZw 🏫 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
@garethjones2293
3 жыл бұрын
It would have been great to show how to do the multiple row steps (2 tick)
@lynnepouliot4268
3 жыл бұрын
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.
@musicisfunpart
3 жыл бұрын
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
@KevinStratvert
3 жыл бұрын
@@musicisfunpart click on the cell with the drop down, go back to data validation, then set it to allow all values.
@musicisfunpart
3 жыл бұрын
@@KevinStratvert Thanks so much. Love your videos. It is very helpful.
@simnerfamily
Жыл бұрын
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
@1binaya
Ай бұрын
same here. spent 2 hours before looking at this comment
@MiguelRodriguez-pk2kp
Ай бұрын
Same here guys!! Did you find a way to work in all the below cells?
@ted4264
24 күн бұрын
I copied created X amount of filtered columns for X amount of rows 😭
@manasoofi7383
3 күн бұрын
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 ?
@SavageGothamChess
3 жыл бұрын
Can we all agree that Kevin's Voice is sooooooooooo calm.
@aveerajsharma
3 жыл бұрын
Not a question to me
@DaleDix
3 жыл бұрын
Yeeessss!
@cbalian
3 жыл бұрын
Agreed. He should do audiobooks.
@Atypical60
3 жыл бұрын
Yes! Trust me, I sound like Bobbi Fleckman and people cringe when they hear me! Kevin should be doing voiceovers!!!!!!!!
@arnaudmanir
11 ай бұрын
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
@jefffunnell
3 жыл бұрын
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!
@KevinStratvert
3 жыл бұрын
My pleasure!
@SteveHinrichs
3 жыл бұрын
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.
@PeteStoraska
Жыл бұрын
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.
@robertredding3875
Жыл бұрын
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
Жыл бұрын
yeah how to get around this???
@Mezamoe
Жыл бұрын
@@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.
@sumans3657
Жыл бұрын
Its not working for n number of coloums
@derangedprotege8624
Жыл бұрын
@@Mezamoe Yeah, but what if you have hundreds of cells?
@ingkaratsrisudjai1328
Жыл бұрын
I'm having the same problem here. Please help.
@crisstones72
3 жыл бұрын
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
@simplelifestyle7327
10 ай бұрын
Look at his own comments under the video.
@flametech179
3 жыл бұрын
That's amazing I was looking something like this only. It will be helping in our day to day life. THANKS KEVIN.
@IwantElvisHair
2 жыл бұрын
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!
@gajabathiarjuntg2390
3 жыл бұрын
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.
@elim198
Жыл бұрын
Best tutorial I have ever watched on KZitem. Perfectly spoken language!!!! Really really appreciate!!!
@stuartpitt6193
2 жыл бұрын
That is brilliant, unbelievable. Even the additional pieces like SORT, UNIQUE. Thank you very much.
@mannysilvajrrealtor
9 ай бұрын
Thanks Kevin! It was easier than I expected only because you explained everything so well.
@tomt102516
3 жыл бұрын
This is the type of video that I am always interested in watching. Thanks for the great content.
@KevinStratvert
3 жыл бұрын
Glad to hear it!
@emilyhammock1058
Жыл бұрын
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?
@cornelialundgren3094
Жыл бұрын
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.
@kuuuyajim
3 жыл бұрын
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! 😘
@KevinStratvert
3 жыл бұрын
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.
@kwojigodfreyedward5744
3 жыл бұрын
Kevin, you are just a magic human
@lenink9788
Жыл бұрын
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!
@swastikszn1781
3 жыл бұрын
Congrats Kevin on 800K subsribers 🎉 now let's march toward 1 million
@lisalarsen2102
2 жыл бұрын
This was soooo easy - I got it first try and I LOVED that you made it pretty by removing the zeros! Brilliant.
@suzannev4317
Жыл бұрын
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.
@evertkleynhans2617
3 жыл бұрын
Wow, Thanks, buddy! Very helpful. Learned a lot.
@FarisGuitar
5 ай бұрын
This was awesome man , just what i was lookin for. very clearly explained and made simple . thanx
@monicamacon2342
Жыл бұрын
IT WAS A GREAT HELP REALLY LIKE THE WAY YOU HIGHLIGHT WHEN TEACHING!
@manzarahmedkhan7244
3 жыл бұрын
Excellent!! Thanks as Always Kevin!
@JustBlankNoInfo
3 жыл бұрын
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!
@paulmreagan
Жыл бұрын
Thanks Kevin - this is exactly the tool(s) I was searching for. Clear explanation. Now to find these menus on the Mac.
@pushkalvashist3637
3 жыл бұрын
Wow I just received the notification for this when I was thinking if doing multiple lists were even possible
@jacktoddy9783
Жыл бұрын
Kevin - thank you - another amazing excel tutorial.
@krishnanraman09
3 ай бұрын
This was easy to understand and awesome. Thanks a ton for creating this video. 😊 ⭐⭐⭐⭐⭐
@neerajpareek5631
9 ай бұрын
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.
@abshukorgandak9776
2 жыл бұрын
Thanks Kevin. This is what i am looking for. Good explanation and Easy to understand.
@sudhakarjain1299
2 жыл бұрын
Wow. I tried other videos and failed to learn, this is so amazing explanantion and I understood easily!
@fittolast2647
4 ай бұрын
Nice work - thank you for this.
@thomasschmidt8544
3 жыл бұрын
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 .
@KevinStratvert
3 жыл бұрын
More to come! 👍
@NTCarpenters
Жыл бұрын
Excellent Video - Easy to follow and worked like a charm
@kurbika
5 ай бұрын
Thank you so much!!!! Super helpful. I just subscribed after watching one video!
@mostafasayed7511
10 ай бұрын
very helpful video, this is exactly what I was looking for. many thanks Kevin.
@cyberlizardcouk
3 жыл бұрын
briliant - i've always wanted to know how to do this.
@submariner99
3 жыл бұрын
Try Leila Gharani if you want Excel tips. She is excellent.
@Iherdit2day
Жыл бұрын
I love how easy you describe each action. Thanks!
@StockSpotlightPodcast
3 жыл бұрын
Nice hash tag tip!
@4Jay384
3 жыл бұрын
Great video! Now I can go a step further with data validation. Thanks Kev
Thank you! This is the much easiest way for me. I watched and other ways but I'll use this.
@towhedaahmed4121
Жыл бұрын
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?
@marquaieteutsch5800
Жыл бұрын
This is exactly what I was needing! You explained it so well! Thank you!
@ondratraveler7415
11 ай бұрын
awesome tutorial. thanks man
@SteveVeach-y3m
9 ай бұрын
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).
@michaelvanderkoon4159
2 жыл бұрын
Great! I didn't know those 3 formulas existed!
@dennishd848
2 жыл бұрын
Hey, Kevin Thank for the video. Indeed it is practically applicable to me as I was looking for this solution for a long time.
@jboutame9113
3 жыл бұрын
OMG. Kevin, you are awesome. I wish I’d known this before retiring. Lol. Great lesson, super well presented.
@JayBee-hb2pm
3 жыл бұрын
Wow you explain excel functions so well.. simple yet effective. Thanks :)
@PrasantKumarV76
7 ай бұрын
This helped me a LOTTTTT!!!! Many Thanks!
@JustinSturrup
11 ай бұрын
Extremely helpful. Thank you.
@JudeMuntadher
Жыл бұрын
Thank you so much. this is very helpful and very well explained.
@KrishnanV9
3 жыл бұрын
Wonderful. I used to use the Indirect function with data validation to achieve this. This is so much better. Thanks
@xaquison
Жыл бұрын
Damn you are good. Easy to follow 👏
@JohnsonKongor
2 жыл бұрын
This is great Kevin. Thanks so much for solving some of my problems.
@sefcontrull
4 ай бұрын
Great video. Thank you
@GeorgeAJululian
Жыл бұрын
Thank you extremely Helpful
@royphilip8486
Жыл бұрын
That was so helpful..Thank you so much Kevin.😀
@danielarias8421
Жыл бұрын
Kevin you are the best!
@surajsai4757
3 жыл бұрын
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...
@alono2323
3 жыл бұрын
Same issue, 2nd row return error..
@KevinStratvert
3 жыл бұрын
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.
@surajsai4757
3 жыл бұрын
@@KevinStratvert it worked, Thanks! Keep up all the great work Kevin.
@alono2323
3 жыл бұрын
@@KevinStratvertnot working for me..the 2nd row (till the end of the DB) is depending on the value on the 1st row.
@lynnepouliot4268
3 жыл бұрын
@@alono2323 That's the same result I get.
@swastikszn1781
3 жыл бұрын
Thanks kevin I was looking for this
@luizcarlosmaiajunior
3 жыл бұрын
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!
@kaiwang7870
2 жыл бұрын
Second to this question. Do we need to have a filtered list for each following line?
@MrJimboCarter
2 жыл бұрын
Third to this question!
@tmichael720
2 жыл бұрын
Fourth. I am completely lost on how this is useful/scalable to add more rows without making filtered lists for each row.
@nirajkumarverma5299
2 жыл бұрын
Really, learned how to create related drop down list👍👍👍
@ryosukefujimura
2 жыл бұрын
Amaizing video. This way is the best because it's easy. I love to do this way instead of INDEX + MATCH.
@ibnathebeliever2665
2 жыл бұрын
impressive! I have subscribed!
@manuelgerardo5973
3 жыл бұрын
Thank you Kevin, this was most helpful!
@maddynewera
7 ай бұрын
thank you bro, its very simple and easy to implement.
@m.m.basetolimishkat5846
4 ай бұрын
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
@taylordebellis3204
6 ай бұрын
This is awesome!
@jessaastrero2919
Жыл бұрын
Thank you so much! Very big help with my project
@cezfiles676
Жыл бұрын
Very useful thank you.
@bingharsan
Жыл бұрын
Very Informative Video, Thank you.
@yee-suenlee2894
Жыл бұрын
Great job, Kevin! Do you just copied the formula for the whole column? In my case, it only work for 1 row? Thoughts!
@balrajvirdee1087
Жыл бұрын
Thanks Kevin 👍🏽, great video
@vinodunny
3 жыл бұрын
Interesting. This is a new way of doing cascading dropdowns in Excel. I was using the INDIRECT function to do the same till now. But using XLOOKUP seems a better option.
@picataggio
2 жыл бұрын
Great Video. Put it to use right away on a CC spreadsheet I keep.
@bharatkulshrestha681
8 ай бұрын
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 ....
@roziawang3652
6 ай бұрын
Thank a lot with the help. it works
@TheSouvikDey
5 ай бұрын
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?
@buboyjones6209
Жыл бұрын
Thanks for the great tutorial!
@KevinStratvert
Жыл бұрын
Glad it was helpful!
@jeffharvey7927
Жыл бұрын
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.
@ninja_dog8124
5 ай бұрын
Came here with this exact question. Did you ever figure it out?! :)
@viveksingh-nt3po
3 жыл бұрын
Very helpful!! Keep it coming!!
@videosGNP
4 ай бұрын
Damn you are GOOD! Thank you!!
@joeybronola6395
2 жыл бұрын
GREAT STUFF KEVIN - THANKS
@FirstnameLastname-cp9lr
Ай бұрын
Bro working at Kevin Cookie Company is such a nice place to work at...Adele messed up bigtime!!
@bijoy99
Жыл бұрын
Great! love this video
@powerclaw
2 жыл бұрын
Great video tutorial, thanks!
@GoodChemistry
2 жыл бұрын
Amazing, thanks for sharing this, it's not the easiest thing to do, but you explain it well! thanks :)
@Khalqistan
3 жыл бұрын
It was great
@azizahmedkhan669
11 ай бұрын
tooooooooo gooooooooood and brilliant trick very easy
@deepanavin8306
2 ай бұрын
very helpful.. thanks
@francoisdutoit2062
3 жыл бұрын
Great thanks! Now, how to bring up less options in the list as you start typing? Sometimes the name list is very long and you want only relevant names to appear as you start typing to select the correct one.
@robinadamowicz4030
3 жыл бұрын
This is so amazing and would make my life so much easier.... if it worked in the desktop app :p Maybe I can convince my team to use Excel online? Fingers crossed!
@thomasarandt5677
2 жыл бұрын
Awesome! Easiest ultimate way!
@jaberaljuaidi
2 жыл бұрын
Hello Kevin, Thank you for the great videos. How can you generate a table of all multiple selections made from the drop down menu.
Пікірлер: 729