Note, after you watch this video make sure you go and watch the updated version where I make it more robust. Here's the link ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
I watched the video, got excited with the solution, and forgot to comment, like and share. I only came back to do just that. Dependent validation inside excel tables had been a nightmare before I watched this video. ❤❤❤❤❤❤❤
I feel as though this should be one of the first lessons you are taught when learning excel. Until you have had years of experience with Excel, you don't realise the power and efficiency of good data validation to reduce errors in manual input. Thank you very much for this!
I'm building a complex data entry template to support month-end bulk upload routines and have been banging my head against the wall for days trying to solve my problem with data validation. Your video addressed all my issues. Thanks!!
Sir you literally saved my life!! been struggling for about two days with my lists and this video finally helped me figure it out! thank you thank you!!!!!
This is brilliant - I tried it a couple of months ago and could not get it to work (bear of little brain syndrome strikes again) but it now does. Amazing. Big thanks to you and Celia
You’re welcome Peter, glad you got it working. Make sure you check out my newer video with a little refinement to this approach. Link is in the description section
The set up of data is key. It is an „old school“ data table, not multirelational, and maybe not sexy. But … I find that many users don’t know how to correctly set up a simple table or database. Thanks for showing that it is still a relevant skill!
This one helped me A LOT!! Finally something "scalable" and easy to adapt to any kind of dataset. Also, this video gave me a better understanding on how lists and array formulas interact. I experimented a bit and could come up with a solution that filling a column with specific expenses in a company (micro view), it would narrow down the options of type of expense, sector and unit/location (all the way up to a macro view)... thank you very much!!
Glad it helped, I’ve also got a slightly updated version. Link is in the description. I appreciate you taking the time to let me know you found it useful
I wish you made this video 1 year ago when I created a massive formula for a multiple dependent data validation drop down that I spent many hours and still use to date. This is incredibly beautiful. I never thought you could use # in this way.
After spending hours looking for what I needed, you showed up! Subscribed! This is perfect for my very detailed drop downs with many options based on the previous drop down. Because of my massive amount of combinations, you made my life so much easier! Thank you for sharing. Can't wait to look at your other videos! :)
This is really cool! I can think of so many ways to use this formula combination! Thank you Wyn for creating/sharing this and Celia for introducing this formula combination! Can not wait to put it to use!
Thanks Wyn - brilliant stuff. It's always been hard to get these to work without using INDIRECT and complicated tables with sub levels. I have some quite volatile lists with sometimes 10-20 options in the level 2/3 lists so I'm thinking I'd probably put the level 2 and 3 choices on separate tabs so I don't get spill errors across the columns. Definitely going to have a play with this. Also, I added a check to the DV at level 2 so it returns "Clear Level 3" (text in a range I named DVErr) so the user can't change level 2 if they already put something in level 3. =IF(E14"",DVErr,XLOOKUP(C14,CatLvl1,CatLvl2)#) Of course, it won't stop people copying and pasting over the DV 🙄😂 so I've left the CF in place as well.
I needed to build something similar to this. The way I've done it is with Name Ranges and List data validation. The bad part is, the cell values were having special characters, so I had to create an intermediary table just to handle this. Now, this approach looks much faster, I'm sure I'm gonna at least try it out! 🔥🔥🔥🔥🔥🔥🔥
Hi, Wyn! Thank you very much for an excellent solution to this problem! Although in 1:43 you say that setting up the validation table is a pain, for me it's the best feature. I already have a big table and need to add more rows where I wish to validate some of the columns. So I find it easy to produce a Pivot Table from the rows I already have, transform it to your validation table and use it to validate the future rows. Again, thank you so much! Kind regards from Argentina.
I have been looking for this formula for a long time and I am glad I found your UA-cam tutorial. Your explanation is very clear and easy to understand. This is the formula I created =TOROW(FILTER(ProjFunding[Funding source ID],ProjFunding[Project contract ID]=M2,"Missing")) and it works well. However, when I copy the formula to the validation list, it is not recognizing this formula and getting "1+1" error message. I would appreciate if you can provide guidance how to resolve this issue. Thanks in advance!
@@AccessAnalytic Appreciate your reply. I used the XLookup function, and it shows the same behavior as show on your video except for the "#" character embedded at the end of the formula. I am getting a #REF error message. Is there any settings in Excel that I need to change?
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then reference to the table headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction.... happy to share my findings with you 😊
Hang on, wasn't this the challenge that you couldn't solve easily in February 2023? Way to go with all these new formulas that Excel is giving us. The conditional formatting I constructed myself about a month ago, glad to see I did it with the same method. Would have saved me some time if I had know about this video. By the way, for $$ in cel references we have F4, is there a shortcut to get the #?
Thank you Wyn! This is so awesome. I've just used this for driving multiple charts for the different drilldown levels of the P&L in an interactive report 😀😀
@@AccessAnalytic I'm finding that in Excel Online the first dependent dropdown isn't working right (doesn't offer a dropdown) but the second one is. Do you find the same thing if you look at the file in the browser? (But it all works fine in the desktop version)
hi. i just took the liberty of quoting tht link to another way of doing a dependant drop down. Thanks Wyn anyway. Your videos are a masterpiece. Kind regards. martin
Hi there Wyn, I wanted to reach out and let you know that I got to your channel after being referred by Celia. I must say, your tutorial was incredible. The way you explained everything was fantastic and simple to understand. 😊😊 I had a question though, is it possible to add a function to the formulas which prevents someone from entering the same data twice in the source list?
Thanks, and thanks to Celia 😀. You can add conditional formatting to highlight duplicates or a formula that checks if there are duplicates. Not sure about preventing duplicates. Maybe a a variant of COUNTIF > 1 inside the validation
This looks great! The main issue I'm having is that data validation won't let me remove the absolute references without giving an error. $A$3 works fine in the data validation list but using $A3 or A3 it says "This entry leads to an error. Try entering different values to continue." and will not let me proceed. It's done this with several techniques now. Any idea what is going on? Edit: I finally figured out what was going on. I had been using the online version of 365, which has limitations and reduced capabilities. When I swapped over to using the desktop version of 365 this method worked perfectly. THIS IS A GAME CHANGER! THANK YOU THANK YOU THANK YOU!!
Not sure sorry. Might be worth posting some screenshots to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589 Or to www.reddit.com/r/excel/
@@AccessAnalytic I was able to figure it out. It was because I was using the online version of 365. When I swapped to using the desktop version of 365 this method worked beautifully! Turns out the online version has many limitations including not having access to Name Manager, which is what ended up tipping me off that the online v desktop versions have different capabilities/limitations. Thank you so so much for this video! This has saved me much grief.
@@AccessAnalytic It was also fairly easy to adapt this to where the 3rd level choice is narrowed down by BOTH the 1st and 2nd choices. In my case, a single supplier (2nd level) can provide materials to multiple jobs (1st level) the PO# (3rd level) not only needs to be associated with the supplier but with the specific job too. The formula you gave shows all PO#s for a supplier regardless of what job the POs are associated with. By adding the *(tbl_SupplierInfo[Job'#]=A4) section to the formula it easily filters out supplier PO#s associated with other jobs and gives me the supplier's POs for the specific job I'm entering data. The *(tbl_Invoices[@Supplier]"") part prevents 0s from showing in the PO selection when someone has entered a job# but not a supplier. Adjusted formula: =TRANSPOSE(SORT(UNIQUE(FILTER(tbl_SupplierInfo[PO'#],(tbl_SupplierInfo[Supplier]=$C4)*(tbl_Invoices[@Supplier]"")*(tbl_SupplierInfo[Job'#]=$A4),"Select supplier first")))) I put this formula on each line to the right of my data entry table then entered =$AS4# in the data validation list formula bar. Works great! Example data used for dropdowns is below: Job# Supplier PO# 23-M-050 Plumbers Supply Company 22-123-456 23-M-050 Trane Technologies 22-987-654 23-M-022 Blackmore & Glunt 1234567-0-CONS 23-M-018 Blackmore & Glunt 9876543-0-CONS 22-M-115 Plumbers Supply Company 1122334-0-CONS 22-M-115 Trane U.S. Inc. 2233445-0-CONS 22-M-114 Plumbers Supply Company 3344556-0-CONS 22-M-114 Stoermer-Anderson Inc. 4455667-0-CONS 22-M-094 Blackmore & Glunt PO1111-OC 22-M-094 Koch Air - Louisville PO2222-OC 22-M-094 Masters' Supply Inc. PO3333-OC
Hi Wyn, great solution! I have a question. Is it possible when I select an item under Dependent, that the first item automatically appears under Sub Dependent? So that an invalid value does not appear first? Looking forward to your solution. :) Kind Regards, Willem Mulder
This is amazing and is literally the only way I’ve been able to make this work. One question. Column 2 of the selection table contains duplicate values, so the Drop Down list for column 3 of the input table is displaying all values associated with the duplicate value. Is there a way to filter column 3 based off not only selection 2, but selection 1 as well?
It's the easiest way but still got minus in it, which the sub 'dependent col' only dependent on the 'dependent col', then we have to change the display or appeareance the entry that not exist. i was hoping for automatic drop down list which really attached to every upper dependent
Thank you for your interest in spreading knowledge. I had an opinion regarding Dynamic Dynamics at minute 3:40 I think there's a lot of potential for this with dynamic arrays. Let me know what you think.
@@AccessAnalytic Can you help me get the solution? Because I tried the equations that were mentioned, I did not get a solution. Please solve them because they are required in a practical way. Thank you for your kindness.
Dear Access Analytics, Many thanks for your video. Very elegant solution. The problem I am experiencing is that I cannot copy the Xlookup formula (Control C) and subsequently paste the formula in the drop-down list (Minute 7:21). When copying the formula or any formula from a cell, excel thinks I am editing the formula cell. On the other hand, if I directly type in the Xlookup formula in the data validation list, the list generates an error (direct formula's in data validation is not possible). Help on the matter is very much appreciated!! Regards,
Hi, Referring to my previous question. 1) I had to press control C + escape to only copy the formula and not edit the cell 2) I can only make the data validation link on the same worksheet as the raw data (no link between other worksheets possible). Many thanks
Yeah notice hi highlight the text from within the formula and then do Ctrl C. Make sure you’ve set up the named ranges and that your formula refers to those named ranges.
An excellent resolution, I truly appreciate it! Nonetheless, there are remaining matters I need to address. In my scenario, there are four columns: the first serves as the primary column, the second relies on the first column, the third is contingent upon a combination of the first and second columns, and the fourth hinges on the interplay of the first, second, and third columns. Do you have any insights into how I can apply your solution in this complex situation?
I will personally address my inquiry, wherein I combine tables using the " | " operator and place them into named ranges named Level2Choice and Level3Choice. Subsequently, within the Data Validation, I employ the following formula: =XLOOKUP(D5 & " | " & E5, Level2Choice, Level3Result) #. - Hope this will be helpful for someone.
how can we do it if level 2 have same value but value 3 have different value. for example: Level 1: A, B, C, level 2: small, medium, large, and level 3: A-small, A-medium, A-large. And value for B level 3 is B-small, B-medium, B-large. As level 2 of A, B, C are the same, can we do it with unique?
Great job.... If I may ask for help? Is this possible? Could the dependant list be dynamic and it applies it to the drop down list? Example... Cell A1 is my dropdown list location. If B2=1.Then.... The drop down list (A1) will show. A, B, C to choose. BUT.... If B2=2 Then A1 drop down list will automatically change the choices to D, E, F. Continued.... But If B2=3 Then my drop down list in A1 will now show choices G, H, I, J, K.
Thank you for your quick response. Funny how I've been trying for months but just asking and watching your video helped. I made it work. I titled each list that the drop down will take from. I then created a cell with multiple Ifs that will input the name. That name shows up on a separate cell. The same cell the dependent drop down list uses to choose the individual list.... AWESOME it works. Very simple.
This is amazing, but I'm still having problems. Can you help? Based on your video, I've developed this formula =SORT(UNIQUE(FILTER(AnnualStmt[Property],(AnnualStmt[Grant]=DataEntry[Grant])*(AnnualStmt[Title]=DataEntry[Title])))), which works perfectly until I try to put it into Data Validation. The xlookup portion of your formula doesn't seem to work when combining two lookups. Any thoughts how I can get this to work?
Really great video. I love the formula. Unfortunately I found that it doesn't work well if the same value in level 2 fits more than 2 values in level 1. For example, I use company name as level 1, city as level 2 and position name as level 3. It turns out that if I select a city at level 2, the result 3 list shows me positions that don't fall in selected level 1. Is there a way to prevent this? Example of the input data table X / London / Driver X / New York / Accountant Y / London / Baker When I select company X, London, the position sheet also shows Baker, and whatever is not in the list for company Y.
Ah yeah, I've taken a look and I see what you mean. I'll have a think about and adjusted formula. The simplest instant fix is to make the values slightly different, maybe with a prefix or extra spaces or underscore
Is there a way to replicate that simple spill function in google sheets? This is the exact solution I need but that missing part kills it for me. Any help would be appreciated, thank you!
@@AccessAnalytic That's alright I appreciate the response and your video helped with the logic side. For those looking in the future, I was unable to replicate the # function in google sheets, I instead used =ArrayFormula(transpose(filter(AC$2:AC,$AB$2:$AB=$D2))) with AC and AB being helper columns (AB - Category | AC - Subcategory) I then used the method shown in this video to create a conditional dropdown based off of the results from the formula which span from cells AE:AU. I had to drag this formula down as far there were categories. With the categories, it's important to stick to the same structure shown in this video. If there are 15 items under one category you should have 15 lines of data - the formula relies on that. Hope this helps someone out there!
Hi Wyn, first time commenting on your channel. My issue is that if you add new or a different set of data to the table, I don't see the drop down list update dynamically. The beauty of spill arrays is that things like drop down list should be dynamic.
Hi, the drop downs should contain new options if data is changed in the table. Yes it’s annoying that the 2nd formula doesn’t spill, but that formula could be copied down 100 or more rows in advance.
Great video. Is it possible to set it up so that only the values in the list are allowed? This only works with the first column (the non dependant column). Thanks!
thank you. This is a very good way to do this. But, I wonder about the formating. When I'm adding this to the file, suddenly Excel stop to work. First it become very slow and then I have to closed it. Any cue of why Excel is reacting like this? If not, no worries, the rest of the video is already much helpfull. Thanks again.
@@ClaudeBalleux yeah, I just tested it on 1,000 and is fine, althrough not much else in my test workbook. If you can open in Excel for web and ty out the new Review > Check Performance button - maybe that will shed some light.
Is there a chance you can make a dependent drop down list , in my case , with some words who contain number ? For example, product Europe , and the project to be Europe 123? I tried different options, but I didn’t solve the problem. Hope you can help
Challenge accepted ;-) I believe I have an easier and cleaner way to do a multiple, repeating drop down list that I haven’t seen demonstrated anywhere yet. How can I send it to you?
@@AccessAnalytic I used another formula to solve it, good for some else that reads the comments: Thanks for the good video. =TRANSPOSE(UNIQUE(FILTER(Categorilist[Sub Kategori]; (Categorilist[Head Kategori]=J3)*(Categorilist[Sub Kategori]""))))
Having to drag a transposed formula was a deal breaker for me. I much prefer doing an IF function in the data validation referring to multiple array lists. Next logical step is power apps.
@@AccessAnalytic yeah it is a significant leap. But they say that think spreadsheets when learning powerFX. You have to create a named range for the array using #. Then write an if A1 = 'Value', list_A,list_B...or create a nest. Copy that expression into the list data validation area.
Note, after you watch this video make sure you go and watch the updated version where I make it more robust. Here's the link ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
After a dozen videos and hours of trying different tips on dependent drop-down lists, FINALLY got one solution that worked. THANK YOU! THANK YOU!
You're welcome. Glad it solved things for you!
Brilliant indeed, Wyn! You simplified a lot and it is just beautiful to see.
Thank you for the shout-out! 💫
Thank you for the inspiration
@@AccessAnalytic I still have a long way to go to pay you off all the inspiration and teachings I have been receiving from you. 😉
Hi Celia, any chance that you know how to make this work (the hash sign specifically) in google sheets?
@@lizaholodovsky9326 hi, Liza. I never tried this functionality in Google Sheets. Sorry. Have you?
The most likely person to know the answer is David Benaim youtube.com/@learnspreadsheets
I watched the video, got excited with the solution, and forgot to comment, like and share.
I only came back to do just that.
Dependent validation inside excel tables had been a nightmare before I watched this video. ❤❤❤❤❤❤❤
Glad it helped. Thanks for taking the time to come back and leave a kind comment
Best Video On Multiple Dependent Dropdown I have ever seen. I was finding the solution for 2 days and finally I got one. Thank you very much!!!
Excellent 😀. Thanks for sharing your thoughts.
I feel as though this should be one of the first lessons you are taught when learning excel. Until you have had years of experience with Excel, you don't realise the power and efficiency of good data validation to reduce errors in manual input. Thank you very much for this!
You’re welcome.
Hi! Thank you for taking time to reply. I got that sorted. 💯
Great!
I'm building a complex data entry template to support month-end bulk upload routines and have been banging my head against the wall for days trying to solve my problem with data validation. Your video addressed all my issues. Thanks!!
Awesome, I appreciate you taking the time to let me know you found it useful.
Thank you so much for doing this, I found you video after spending several hours trying to find a solution. This is precisely what I was looking for.
Excellent :) Make sure you check out the slightly updated version here: ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=2hA6M9IMZWRVp_jP
Bloody awesome! I used indirect formulas, but your new method is the winner.
Thanks Bhavik!
Sir you literally saved my life!! been struggling for about two days with my lists and this video finally helped me figure it out! thank you thank you!!!!!
I appreciate you taking the time to let me know you found it useful
The laboratory at full capacity. It is certainly an amazing result. Thanks Wyn!
Thank you Iván
This is brilliant - I tried it a couple of months ago and could not get it to work (bear of little brain syndrome strikes again) but it now does. Amazing. Big thanks to you and Celia
You’re welcome Peter, glad you got it working. Make sure you check out my newer video with a little refinement to this approach. Link is in the description section
I don't leave comments on videos but when I do... I completely gutted my solution! Thank you!
You’re welcome, thanks for making an exception and commenting!
The set up of data is key. It is an „old school“ data table, not multirelational, and maybe not sexy. But … I find that many users don’t know how to correctly set up a simple table or database. Thanks for showing that it is still a relevant skill!
You’re welcome
You should be MVP of the year!
😆 hah! Thanks 😊
This one helped me A LOT!! Finally something "scalable" and easy to adapt to any kind of dataset.
Also, this video gave me a better understanding on how lists and array formulas interact. I experimented a bit and could come up with a solution that filling a column with specific expenses in a company (micro view), it would narrow down the options of type of expense, sector and unit/location (all the way up to a macro view)... thank you very much!!
Glad it helped, I’ve also got a slightly updated version. Link is in the description. I appreciate you taking the time to let me know you found it useful
I wish you made this video 1 year ago when I created a massive formula for a multiple dependent data validation drop down that I spent many hours and still use to date. This is incredibly beautiful. I never thought you could use # in this way.
Thank you Ebru
To be honest, I'm so so jealous of you havin Office 365. Creating this solution for office 2019 truly creates a pain in my life :D
I hope it won’t be long before all organisations realise that 365 is the only way to go
After spending hours looking for what I needed, you showed up! Subscribed! This is perfect for my very detailed drop downs with many options based on the previous drop down. Because of my massive amount of combinations, you made my life so much easier! Thank you for sharing. Can't wait to look at your other videos! :)
Awesome! Thank you!. I appreciate you taking the time to let me know you found it useful and leaving a kind comment
Fantastic!!
Cheers
You’re a legendary, amazing person! Thanks for sharing. Not only a nice tutorial but also a free downloadable file
Cheers, share the content with others! 🙏🏼
I'll be using this for one tracker. Thank you, Wyn!
You’re welcome.
Exceptional. I've been trying this different ways for over two years. This is by far the best. Great approach and great tutorial. thank you!!
Glad it helped 😀
This is really cool! I can think of so many ways to use this formula combination!
Thank you Wyn for creating/sharing this and Celia for introducing this formula combination!
Can not wait to put it to use!
Thank you. Hope it proves useful 😀
Thanks Wyn - brilliant stuff. It's always been hard to get these to work without using INDIRECT and complicated tables with sub levels. I have some quite volatile lists with sometimes 10-20 options in the level 2/3 lists so I'm thinking I'd probably put the level 2 and 3 choices on separate tabs so I don't get spill errors across the columns. Definitely going to have a play with this.
Also, I added a check to the DV at level 2 so it returns "Clear Level 3" (text in a range I named DVErr) so the user can't change level 2 if they already put something in level 3.
=IF(E14"",DVErr,XLOOKUP(C14,CatLvl1,CatLvl2)#)
Of course, it won't stop people copying and pasting over the DV 🙄😂 so I've left the CF in place as well.
Nice Shirley. Yep separate sheets is the way to go.
This guy is a genious. A little bit scary, but a genious.
Thanks 😀 ( I think 😂 )
Unbelievable !! After 100 videos this is the only way tbhat worked perfectly for me!!
Awesome to hear. Thanks for taking the time to let me know 😀
I needed to build something similar to this. The way I've done it is with Name Ranges and List data validation. The bad part is, the cell values were having special characters, so I had to create an intermediary table just to handle this.
Now, this approach looks much faster, I'm sure I'm gonna at least try it out!
🔥🔥🔥🔥🔥🔥🔥
I hope it works out for you 👍🏼
Thank you so much! This has been a great help!
You’re welcome. Thanks for taking the time to leave a kind comment
Hi, Wyn! Thank you very much for an excellent solution to this problem! Although in 1:43 you say that setting up the validation table is a pain, for me it's the best feature. I already have a big table and need to add more rows where I wish to validate some of the columns. So I find it easy to produce a Pivot Table from the rows I already have, transform it to your validation table and use it to validate the future rows. Again, thank you so much! Kind regards from Argentina.
Good stuff, thanks for the tip!
I have been looking for this formula for a long time and I am glad I found your UA-cam tutorial. Your explanation is very clear and easy to understand. This is the formula I created =TOROW(FILTER(ProjFunding[Funding source ID],ProjFunding[Project contract ID]=M2,"Missing")) and it works well. However, when I copy the formula to the validation list, it is not recognizing this formula and getting "1+1" error message. I would appreciate if you can provide guidance how to resolve this issue. Thanks in advance!
Thanks. The list input box can only accept a formula that returns a range. FILTER is not a function that does that while XLOOKUP is
@@AccessAnalytic Appreciate your reply. I used the XLookup function, and it shows the same behavior as show on your video except for the "#" character embedded at the end of the formula. I am getting a #REF error message. Is there any settings in Excel that I need to change?
No settings to change. You say you’re not using the # - is there a reason?
beautifully done!
Thank you 😀
Nice video. Also, I have found an efficient and v. simple way to reference to every table in workbook in a drop down list then reference to the table headers in another drop down list and finally get a unique list of that column in a 3rd dependent drop down list.... and Only using the INDIRECT fuction....
happy to share my findings with you 😊
Thanks, I’ve come across a few INDIRECT methods and prefer this approach. Thanks though.
Excellent!
Crisp and Clean. 🙂
Thanks
You’re welcome. Thanks for the feedback
wow, what a great formula. Thnakyou
You’re welcome. 😀
Hang on, wasn't this the challenge that you couldn't solve easily in February 2023? Way to go with all these new formulas that Excel is giving us.
The conditional formatting I constructed myself about a month ago, glad to see I did it with the same method. Would have saved me some time if I had know about this video.
By the way, for $$ in cel references we have F4, is there a shortcut to get the #?
Yep, was inspired by another MVPs solution that gave the key part to solving this
Super cool, thanks Wyn!
Cheers Chris
Fantastic so very interesting. I'll have to try this out. Mind blowing. Thank you !
You’re welcome, thank you 😀
And there's me thinking Die Hard was a Christmas movie...great technique though!
Next video..,, how to handle movies in multiple categories 🤣
Thank you Wyn! This is so awesome. I've just used this for driving multiple charts for the different drilldown levels of the P&L in an interactive report 😀😀
Nice one Patrick !
@@AccessAnalytic I'm finding that in Excel Online the first dependent dropdown isn't working right (doesn't offer a dropdown) but the second one is. Do you find the same thing if you look at the file in the browser? (But it all works fine in the desktop version)
@@patrickleavydatadrivenfina1491 , both are working for me online
hi. i just took the liberty of quoting tht link to another way of doing a dependant drop down. Thanks Wyn anyway. Your videos are a masterpiece. Kind regards. martin
Very kind of you to say so 😊, thanks Martin
Hi Martin, not sure what you mean by the quoting link comment
Simply beautiful!
Thanks for taking the time to leave a kind comment
Thank you for this video. Clear, simple and effective!!
You’re welcome
This just saved my weekend!! This was awesome!!
Glad to help ☺️
Brilliant! Thank you for this!
You’re welcome
My head exploded! Thank you so much for this!
You're welcome, I think 🤣
Great video. Thanks for sharing Wyn!
Cheers Cristian
THANK YOU SO MUCH, IT REALLY HELPS A LOT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!111111
You’re welcome 😀
Hi there Wyn, I wanted to reach out and let you know that I got to your channel after being referred by Celia. I must say, your tutorial was incredible. The way you explained everything was fantastic and simple to understand. 😊😊
I had a question though, is it possible to add a function to the formulas which prevents someone from entering the same data twice in the source list?
Thanks, and thanks to Celia 😀.
You can add conditional formatting to highlight duplicates or a formula that checks if there are duplicates.
Not sure about preventing duplicates. Maybe a a variant of COUNTIF > 1 inside the validation
@@AccessAnalytic Thank you so much!
Amazing, this is a life changing 💛
Hah ! 😊. Glad it helps
Thanks Wyn, amazing video. 🙏🙏🙏🙏
Cheers 😀
Great job, cool. Thank you!
Thank you Luciano
Useful video, thanks
You’re welcome
Many Thanks. That is brilliant
Thanks, you're welcome
Love the video, thank you for sharing
You’re welcome
THANK YOU!
You're welcome
it is the so far work arable thanks
You’re welcome
just wow! complete solution, like it
Thank you. I appreciate you taking the time to let me know you found it useful
This looks great! The main issue I'm having is that data validation won't let me remove the absolute references without giving an error. $A$3 works fine in the data validation list but using $A3 or A3 it says "This entry leads to an error. Try entering different values to continue." and will not let me proceed. It's done this with several techniques now. Any idea what is going on?
Edit: I finally figured out what was going on. I had been using the online version of 365, which has limitations and reduced capabilities. When I swapped over to using the desktop version of 365 this method worked perfectly. THIS IS A GAME CHANGER! THANK YOU THANK YOU THANK YOU!!
Not sure sorry. Might be worth posting some screenshots to techcommunity.microsoft.com/t5/microsoft-excel/ct-p/Excel_Cat?WT.mc_id=M365-MVP-5002589
Or to www.reddit.com/r/excel/
@@AccessAnalytic I was able to figure it out. It was because I was using the online version of 365. When I swapped to using the desktop version of 365 this method worked beautifully! Turns out the online version has many limitations including not having access to Name Manager, which is what ended up tipping me off that the online v desktop versions have different capabilities/limitations. Thank you so so much for this video! This has saved me much grief.
@@AccessAnalytic It was also fairly easy to adapt this to where the 3rd level choice is narrowed down by BOTH the 1st and 2nd choices. In my case, a single supplier (2nd level) can provide materials to multiple jobs (1st level) the PO# (3rd level) not only needs to be associated with the supplier but with the specific job too.
The formula you gave shows all PO#s for a supplier regardless of what job the POs are associated with. By adding the *(tbl_SupplierInfo[Job'#]=A4) section to the formula it easily filters out supplier PO#s associated with other jobs and gives me the supplier's POs for the specific job I'm entering data. The *(tbl_Invoices[@Supplier]"") part prevents 0s from showing in the PO selection when someone has entered a job# but not a supplier.
Adjusted formula: =TRANSPOSE(SORT(UNIQUE(FILTER(tbl_SupplierInfo[PO'#],(tbl_SupplierInfo[Supplier]=$C4)*(tbl_Invoices[@Supplier]"")*(tbl_SupplierInfo[Job'#]=$A4),"Select supplier first"))))
I put this formula on each line to the right of my data entry table then entered =$AS4# in the data validation list formula bar. Works great!
Example data used for dropdowns is below:
Job# Supplier PO#
23-M-050 Plumbers Supply Company 22-123-456
23-M-050 Trane Technologies 22-987-654
23-M-022 Blackmore & Glunt 1234567-0-CONS
23-M-018 Blackmore & Glunt 9876543-0-CONS
22-M-115 Plumbers Supply Company 1122334-0-CONS
22-M-115 Trane U.S. Inc. 2233445-0-CONS
22-M-114 Plumbers Supply Company 3344556-0-CONS
22-M-114 Stoermer-Anderson Inc. 4455667-0-CONS
22-M-094 Blackmore & Glunt PO1111-OC
22-M-094 Koch Air - Louisville PO2222-OC
22-M-094 Masters' Supply Inc. PO3333-OC
@tiffanydrouin2622 - glad you figured it out 😀
Great stuff
man you are awesome
Hah 😊, too kind!
Hi Wyn, great solution! I have a question. Is it possible when I select an item under Dependent, that the first item automatically appears under Sub Dependent? So that an invalid value does not appear first? Looking forward to your solution. :) Kind Regards, Willem Mulder
Thanks, sounds like the type of thing that would need some VBA.
This is amazing and is literally the only way I’ve been able to make this work. One question. Column 2 of the selection table contains duplicate values, so the Drop Down list for column 3 of the input table is displaying all values associated with the duplicate value. Is there a way to filter column 3 based off not only selection 2, but selection 1 as well?
There is a way that I found after someone else pointed out the same thing. I need to do another video on it in the next few weeks.
The simplest ( non ideal ) quick fix is to avoid duplicates via a prefix / extra space
Took more than a few weeks of waiting ( 4 months! ) but update is here ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
Hi, excellent video. Can you tell me what excel functions are directly supported in the Data Validation Allow: & Source: field boxes..Thank you
I’m honestly not sure sorry.
It's the easiest way but still got minus in it, which the sub 'dependent col' only dependent on the 'dependent col', then we have to change the display or appeareance the entry that not exist. i was hoping for automatic drop down list which really attached to every upper dependent
Yeah, I need to do a follow up video that solves that issue
Thank you 😀
You’re welcome
Super Information 👌
Thanks, much appreciated.
Thank YOU sooooooo much
😀 you’re welcome
Righto, well I've been scouring the internet to solve this for a long time, lets see if your vid leads to a subscribe.
🤷🏻♂️
Thank you for your interest in spreading knowledge. I had an opinion regarding Dynamic Dynamics at minute 3:40
I think there's a lot of potential for this with dynamic arrays. Let me know what you think.
I’m not sure what your question is?
@@AccessAnalytic I mean, there is a solution to create an automatic filtering function, which you will explain in a minute ٣:٤٠
@@tamersalem7542 ah... is that using MAP/MAKEARRAY or some similar technique?
@@AccessAnalytic Can you help me get the solution? Because I tried the equations that were mentioned, I did not get a solution. Please solve them because they are required in a practical way. Thank you for your kindness.
Sorry I don’t understand what the problem is
Amazing ❤❤
Thank you 😀
Jeeesus! I was looking for this for... ages! Congratulations for this great data dynamic validation and thank you for the great tutorial.
You’re welcome. I appreciate you taking the time to let me know you found it useful
Big thanks it was amazing 😊
You're welcome 😀
I LOVE YOU
🤣
Dear Access Analytics,
Many thanks for your video. Very elegant solution.
The problem I am experiencing is that I cannot copy the Xlookup formula (Control C) and subsequently paste the formula in the drop-down list (Minute 7:21). When copying the formula or any formula from a cell, excel thinks I am editing the formula cell. On the other hand, if I directly type in the Xlookup formula in the data validation list, the list generates an error (direct formula's in data validation is not possible).
Help on the matter is very much appreciated!!
Regards,
Hi,
Referring to my previous question.
1) I had to press control C + escape to only copy the formula and not edit the cell
2) I can only make the data validation link on the same worksheet as the raw data (no link between other worksheets possible).
Many thanks
Yeah notice hi highlight the text from within the formula and then do Ctrl C.
Make sure you’ve set up the named ranges and that your formula refers to those named ranges.
This solved half of my problem. What can I do, when the result of list3 depends on the combination of list1 and list2 ?
Hi, take a look at the comment by Tiffany, where they explain an approach
Here's my updated solution ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
Excellent 👍
Cheers
Is it possible to apply in Google sheet ? Or any alternative ?
I’ve never used Google sheets sorry
This is beautiful technique , does the hash sign at the end make any formula return an array ??
Thanks, no only those that return a range ( little known fact that XLOOKUP returns a range ).
Will work with INDEX
This is awesome! Is there any way to do the same thing in Google Sheets?
Thanks 😀, I’ve not used Google sheets sorry.
Awesome video❤...How many dependent drop down lists can we create using this method? I wanted to create drop down lists to 7 level choice ( 7 columns)
Thanks, it’s unlimited really
An excellent resolution, I truly appreciate it! Nonetheless, there are remaining matters I need to address. In my scenario, there are four columns: the first serves as the primary column, the second relies on the first column, the third is contingent upon a combination of the first and second columns, and the fourth hinges on the interplay of the first, second, and third columns. Do you have any insights into how I can apply your solution in this complex situation?
I will personally address my inquiry, wherein I combine tables using the " | " operator and place them into named ranges named Level2Choice and Level3Choice. Subsequently, within the Data Validation, I employ the following formula: =XLOOKUP(D5 & " | " & E5, Level2Choice, Level3Result) #. - Hope this will be helpful for someone.
Glad you solved it
@@0rtli : check it out ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
how can we do it if level 2 have same value but value 3 have different value. for example: Level 1: A, B, C, level 2: small, medium, large, and level 3: A-small, A-medium, A-large. And value for B level 3 is B-small, B-medium, B-large. As level 2 of A, B, C are the same, can we do it with unique?
Check out this version:
ua-cam.com/video/BoAtpZIf_oY/v-deo.html
Great job.... If I may ask for help? Is this possible?
Could the dependant list be dynamic and it applies it to the drop down list?
Example...
Cell A1 is my dropdown list location.
If B2=1.Then....
The drop down list (A1) will show. A, B, C to choose.
BUT....
If B2=2
Then A1 drop down list will automatically change the choices to D, E, F.
Continued.... But
If B2=3
Then my drop down list in A1 will now show choices G, H, I, J, K.
It’s possible if you just want A1 as the drop down , very difficult ( I can’t think how ) if you then want multi dependent drop downs feeding off a1
Thank you for your quick response. Funny how I've been trying for months but just asking and watching your video helped. I made it work.
I titled each list that the drop down will take from. I then created a cell with multiple Ifs that will input the name. That name shows up on a separate cell. The same cell the dependent drop down list uses to choose the individual list.... AWESOME it works. Very simple.
@freepilot7732 glad you solved it!
This is amazing, but I'm still having problems. Can you help? Based on your video, I've developed this formula =SORT(UNIQUE(FILTER(AnnualStmt[Property],(AnnualStmt[Grant]=DataEntry[Grant])*(AnnualStmt[Title]=DataEntry[Title])))), which works perfectly until I try to put it into Data Validation. The xlookup portion of your formula doesn't seem to work when combining two lookups. Any thoughts how I can get this to work?
Thanks, Not sure sorry. There’s lots of little traps and restrictions when trying to get this to work
Really great video. I love the formula. Unfortunately I found that it doesn't work well if the same value in level 2 fits more than 2 values in level 1. For example, I use company name as level 1, city as level 2 and position name as level 3.
It turns out that if I select a city at level 2, the result 3 list shows me positions that don't fall in selected level 1. Is there a way to prevent this?
Example of the input data table
X / London / Driver
X / New York / Accountant
Y / London / Baker
When I select company X, London, the position sheet also shows Baker, and whatever is not in the list for company Y.
Sounds like the master table might not be set up correctly in that case. I’ll have a check to see if I can replicate in a few days time.
Ah yeah, I've taken a look and I see what you mean. I'll have a think about and adjusted formula. The simplest instant fix is to make the values slightly different, maybe with a prefix or extra spaces or underscore
I’ve found a solution, I’ll post a link to a fine once I’ve finished it
Took a long time for me to get around to the update but here it is: ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
Thank you so much! I cant wait to see it.:) @@AccessAnalytic
Why can't you use a Filter() function in a Data Validation drop down but you can use an XLookup()?
XLOOKUP actually returns a range reference under the hood, whereas FILTER does not.
Is there a way to replicate that simple spill function in google sheets? This is the exact solution I need but that missing part kills it for me. Any help would be appreciated, thank you!
I’ve never used Google sheets sorry
@@AccessAnalytic That's alright I appreciate the response and your video helped with the logic side.
For those looking in the future, I was unable to replicate the # function in google sheets, I instead used =ArrayFormula(transpose(filter(AC$2:AC,$AB$2:$AB=$D2))) with AC and AB being helper columns (AB - Category | AC - Subcategory) I then used the method shown in this video to create a conditional dropdown based off of the results from the formula which span from cells AE:AU. I had to drag this formula down as far there were categories. With the categories, it's important to stick to the same structure shown in this video. If there are 15 items under one category you should have 15 lines of data - the formula relies on that.
Hope this helps someone out there!
Hi Wyn, first time commenting on your channel. My issue is that if you add new or a different set of data to the table, I don't see the drop down list update dynamically. The beauty of spill arrays is that things like drop down list should be dynamic.
Hi, the drop downs should contain new options if data is changed in the table.
Yes it’s annoying that the 2nd formula doesn’t spill, but that formula could be copied down 100 or more rows in advance.
Hi, can I use Full Excel features, Power BI and other Microsoft analytics tools on MAC, or it's not supported?
Hi, not supported. Elements of Power Query have been added to Mac Excel 365 but no Power Pivot and no Power BI
Great video. Is it possible to set it up so that only the values in the list are allowed? This only works with the first column (the non dependant column). Thanks!
If validation doesnt work for the xlookup columns you need to uncheck the ignore blanks option.
Glad you worked it out. It was on my list to double check and come back to you.
@@SBDH070 : Check it out: ua-cam.com/video/BoAtpZIf_oY/v-deo.htmlsi=ybJkE55oux97_AAW
awesome🤩
Thank you!
like a magic ! :)
🪄✨
thank you. This is a very good way to do this. But, I wonder about the formating. When I'm adding this to the file, suddenly Excel stop to work. First it become very slow and then I have to closed it. Any cue of why Excel is reacting like this? If not, no worries, the rest of the video is already much helpfull. Thanks again.
How many cells are you applying the conditional formatting to?
@@AccessAnalytic Only 60. This is weird. is'n it?
@@ClaudeBalleux yeah, I just tested it on 1,000 and is fine, althrough not much else in my test workbook. If you can open in Excel for web and ty out the new Review > Check Performance button - maybe that will shed some light.
Hi, what about very long lists that I can't add horizontal due to number of cells each?
I’m not sure what you mean sorry
@@AccessAnalytic it's ok. Your recommendation actually ended up working 😊 thank you
@Larida101 you’re welcome
Is there a chance you can make a dependent drop down list , in my case , with some words who contain number ? For example, product Europe , and the project to be Europe 123? I tried different options, but I didn’t solve the problem. Hope you can help
I don’t understand sorry, what’s the reason you can’t use the technique in the video?
It didn't work for Google sheets :(
Google sheets is a very different product to Excel
Challenge accepted ;-) I believe I have an easier and cleaner way to do a multiple, repeating drop down list that I haven’t seen demonstrated anywhere yet. How can I send it to you?
info@accessanalytic.com.au 🙂
@@AccessAnalytic Workbook sent... Enjoy!
I have excel 2021, and dont have =TOROW formula
Yes sadly the one-off versions never get the new features.
@@AccessAnalytic
I used another formula to solve it, good for some else that reads the comments: Thanks for the good video. =TRANSPOSE(UNIQUE(FILTER(Categorilist[Sub Kategori]; (Categorilist[Head Kategori]=J3)*(Categorilist[Sub Kategori]""))))
@SuryoyoEntertainment I’m surprised you have UNIQUE but not TOROW. I’m guessing you are using Excel 2021?
@@AccessAnalytic Yeah, 2021. not 365
Isn't this just like a normal filter?
No, this is for providing dependent drop down lists
Having to drag a transposed formula was a deal breaker for me. I much prefer doing an IF function in the data validation referring to multiple array lists. Next logical step is power apps.
I think Power Apps is a significant leap of learning from this solution. How does the IF in a validation list work?
@@AccessAnalytic yeah it is a significant leap. But they say that think spreadsheets when learning powerFX. You have to create a named range for the array using #. Then write an if A1 = 'Value', list_A,list_B...or create a nest. Copy that expression into the list data validation area.
Ah ok. Thanks