Even easier than easiest - Multi Level Dependent Drop Down Lists

[@BN-yt4et]

I never had a method to start with, and was looking for help with my spreadsheet and this is INCREDIBLE. This is so clear and easy(?!) I'm actually excited to try it out as soon as I'm home! Thank you so much for sharing 🎉

[@colinhuntley9729]

wyn and yourself are really a couple of excel experts. thanks for all of your hardwork!

[@AccessAnalytic]

Cheers Colin 😀

[@RichardJones73]

That was excellent work to come up with that Lambda function. Now I am gonna convert all my projects so that I have a use for multiple drop down lists :)

[@Jim-zm6fw]

Hi Mark: Thank you so much. I subscribe to both your and Wyn's websites and videos. You never cease to amaze me, and are certainly making me a better Excel user!!

[@ExcelOffTheGrid]

Wow, thank you! That is very kind of you to say. 😁

[@anilb5836]

Thank you very Sir for inventing such a nice and easy trick for dependent drop down .... Very much appreciated 👍

[@thebeginnerartist127]

Great work mark, I tried to create a better version of DDL function, and this formula has two extra advantages over DDL function, First - It has no limits of levels, although 10 levels are more than enough, but still, Second - In DDL function, suppose user forgets to enter data of level 2, and is entering data in level 3, user gets to see the list of level 2, but using my formula, user will only be able to view list of the current level if he fills the data in previous levels properly, Formula - =LAMBDA(range,[level],[previous_levels], IF(ISOMITTED(level), LET(ref,MAP(range,LAMBDA(a,CELL("address",a))), formula,INDIRECT(CONCAT(INDEX(ref,1,1),":",INDEX(ref,COUNTA(CHOOSECOLS(ref,1)),1))), formula), LET(ref,MAP(range,LAMBDA(a,CELL("address",a))), l_array,BYROW(CHOOSECOLS(range,SEQUENCE(level-1,,1,1)),CONCAT), r_array,CHOOSECOLS(ref,level), formula,INDIRECT(CONCAT(XLOOKUP(previous_levels,l_array,r_array),":",XLOOKUP(previous_levels,l_array,r_array,,,-1))), formula))) Read these example formulas and you be able to understand arguments, Level 1 - DDL($R$1#) Level 2 - DDL($R$1#,2,A1) Level 3 - DDL($R$1#,3,A1&B1) Level 4 - DDL($R$1#,4,A1&B1&C1) Level 15 - DDL($R$1#,15,A1&B1&C1&D1&E1&F1&G1&H1&I1&J1&K1&L1&M1&N1&O1) I have made one another setup for this multiple level dropdown thing, and I think that is the best of all, please provide your mail so I can mail you file.

[@andrewh684]

I like this due to the second advantage you list. Just to point out that the formula you have shown doesn't work in the validation list, tweaked it slightly below and tested. =LAMBDA(range,[level],[previous_levels], IF(ISOMITTED(level), LET(ref,MAP(range,LAMBDA(a,CELL("address",a))), formulaINDIRECT(CONCAT(INDEX(ref,1,1),":",INDEX(ref,COUNTA(CHOOSECOLS(ref,1)),1))), formula), LET(ref,MAP(range,LAMBDA(a,CELL("address",a))), l_array,BYROW(CHOOSECOLS(range,SEQUENCE(level-1,,1,1)),LAMBDA(x,CONCAT(x))), r_array,INDEX(ref,,level), formula,INDIRECT(CONCAT(XLOOKUP(previous_levels,l_array,r_array),":",XLOOKUP(previous_levels,l_array,r_array,,,-1))), formula)))

[@thebeginnerartist127]

@@andrewh684 My formula is tested and is working fine in data validation list, I can share file link if you say, there may be other reason if it is not working for you.

[@SM-qn1hs]

@@andrewh684 Mine is also not working for the validation list. Moreover, when I use your formula, it shows "you can't include a parameter to a Let function after defining its calculation.". Can you please help?

[@McKaySavage]

Very cool direction for solving the challenge of dependent drop downs. Really like it!

[@ExcelOffTheGrid]

Thanks, I’m glad you liked it. 😁

[@AccessAnalytic]

Excellent 😀I realised one thing we need to call out is this only works on newer versions of 365 where the duplicates are automatically removed from the drop down.

[@shabuthambi6512]

Excellent Excel off the grid. Job made easier!. Thanks for your hard work.

[@ExcelOffTheGrid]

You are welcome! I hope you can put it to good use.

[@stefankirst3234]

Easy to use? YES! - Easy to understand? NO 🤯 - Thanks for sharing this!

[@KrishnaKumar-zn9kg]

Love you guys! Super tip!

[@vacilando86]

Woaw, that's another level trick, really liked it.

[@diannedrechsler4792]

Wow! this is amazing! Thanks for sharing it. Love it.

[@arbazahmad7177]

Excellent..🎉 easier than ever... 🎉 thanks for sharing 😊

[@EricaDyson]

Nice. SVery nice indeed. Thanks for your hard work, creativity and sharing.

[@GrainneDuggan_Excel]

And we have a winner! 🎉🎉💥🌟✨️❤

[@ExcelOffTheGrid]

Team work makes the dream work 😁

[@JodoKast1221]

I have a workbook which I put historical monthly sales data into. I have it set up so I can choose months from three drop-down lists and it will show me the sales from those months so I can compare them. At first I was just using a standard lists of months for the data validation drop down list but I was never happy that I was able to choose the same months in different columns or months out of order so I used a table to generate the months for data validation drop down lists that updates and changes based on what is chosen in the drop-down lists coupled with an IF and XLOOKUP formula for the data validation drop down lists. It literally took me an entire day of trial and error to get it working because the table which updates needed formulas for multiple scenarios but now each drop down list only allows months which will not interfere with the other either of the other drop down lists and the lists change based on choices made in any other drop down list. I think I got lucky and stumbled my way to a solution but I would love to see how a pro like you (Mark) would approach this problem.

[@truewiking]

Great solution, 👏

[@ahmedeid5381]

you're amazing ❤😊

[@hildy6362]

Tested on Excel Online and Excel for Mac. The DDL function, once set up in a workbook in a compatible Windows Excel version, travels with the workbook. It appears to work fine in Excel online, but Excel for Mac had problems with it. In Excel for Mac, the drop down list contains the filtered array including al incidences of the filter values - it does automatically filter again to unique values. I tried using the UNIQUE function in various ways as a work-around with no success. The dynamic characteristics seem to work, the list is filtered to the correct values based on previous cells, but the drop down will list all of the incidents of the filtered values (the drop down list may contain only 3 unique values, but will also include all the repeats of those three values.). Won't work well on the Mac, especially with larger lists. Still a great technique if you are only working with a Windows or Online version of Excel. Thanks guys.

[@VideoAndrega]

Thank for sharing this. Good job both of you. Nevertheless I replaced dropdown lists by slices because they allow me multiples sélections amon sublists and I do never need to limit the choice to a single item.

[@IvanCortinas_ES]

Mark absolutely in Magic mode! Don't miss it. Enjoy!!! Thanks for the early Christmas gifts...

[@SúnionQuímica]

Wuow! Awesome job, is there any way to implement this on excel web?

[@ajithchandbhandaari]

great

[@chrism9037]

Another great video, thanks Mark! Could you paste the code into the personal macro workbook, and then set up an icon on the QAT based on a macro, to be able to copy and paste from the personal macro workbook to any other workbook?

[@DataVisualisation]

... I would install the Monkey from Ken Puls. Then you can save lambdas in the DB and insert them into any sheet you want. Monkey has the Lambda feature in the free version and is super easy to use.

[@johnjoecawleyie6004]

Hi Mark, great videos from you and your mate. Is there any way we can add custom formatting to it, to highlight cells when they no longer match the preceding column?

[@ExcelOffTheGrid]

Yes there is - see this: Don't trust data validation in Excel! | Excel Off The Grid kzbin.info/www/bejne/j5e0e2V4fax1gMk

[@The1ShyButterfly]

BRILLIANT 👏 👏 👏 Question: How can we include these DDL functions in our Personal Workbook (VBA)? so it is always available THANK YOU for your brilliant solutions, helping us to be more efficient ❤

[@NeerajSharma-zt2gj]

Great 👍

[@NeerajSharma-zt2gj]

Sir, is there a limit on number of rows to work on?

[@ExcelOffTheGrid]

I believe is approx 30k - but not sure of the exact number.

[@merriganman]

REALLY like the approach to this! So simple! Had a variation of Wyn's previous method as my go-to dropdown solution, but the hierarchy of the products I was working with required Multiple sheets of data validation. Only problem I've found with this is it doesn't like BLANKS too much. I have a column with a product attribute that is not applicable to some, so is just left blank. The DDL function returns "0" as the spilled range for the dropdown and requires that to be selected for the next dropdown to populate. Blank won't work. While "0" might technically be correct, it suggests that this product could possibly have this type attribute added to it in production when it cannot. Would there be a way to skip a lookup, ie. =DDL(Range, Lookup1, Lookup2, ,Lookup4) ?

[@kdfarmery]

Hi Mark, thank you for this - this is going to save a lot of time - just one question, is it possible to embed these two functions within Excel? or is it a case of copying it from one workbook to another? Thanks again.

[@esrAsnataS]

That's quality.

[@RavneetSingh-rr6le]

🔥🔥🔥

[@DanThorne54]

Hi Mark @ExcelOffTheGrid - I love your awesome tutorials! I have a unique problem with my dependent drop-down list use case. I built a cartesian / cross-join table that has 5 columns and 43,399 records, containing all of the possible combinations. I read somewhere that data validations might be limited to 32,767 records, while trying to problem solve why only 10 of the 12 unique values appeared in the first column's drop-down list. I tried using both the "0234 Dependent drop-down lists" (as a Plan A) and the DDL custom function "0238 Even easier dependent drop down lists" (as a Plan B), with the same result. Do you think there's another workaround I could use to solve this?

[@dinethprabash1001]

Can I add this to a template so that it starts up with Excel by default?

[@edme1055]

just WAAAAUW!

[@ExcelOffTheGrid]

Thanks 😁

[@paulclarke4894]

This is ok if you're running one of the latter versions of Office but it's not going to work on earlier versions that don't support Lambda functions that in the range name manager.

[@XLarium]

I will wait for 3 months when you come up wtih an even better solution. 🙂

[@RichardJones73]

Or wait 10 years before Microsoft create a new formula for just this

[@brad3854]

I was thinking structured references when looking at DDLSorter code and tried this: "=SORT(Data,{1,2,3})" to produce "Grouped/Sorted". It worked, is volatile, and since it references the Data table, it automatically adjusts when rows are added to the table. Using Excel 2021 so this won't work in earlier versions, but lambda does not work in my version. Enjoyed the video and wrote some xlwings python to implement the DDL code (same reason, lambda not available).

[@KaanSiyasal]

Is it possible to insert/copy the function you created and posted in the video explanation to the personal macro workbook, so that it's available each and every workbook that has been opened, whether a new or pre-created??

[@moirraine]

I am not a super expert in Excel and I admit I don't understand the DDL formula AT ALL so I can't even play around with it, but.. Here is what I noticed. Once my sorted list exceeds 255 rows, the whole thing stops working. My "Data" is several thousands of rows long and even adding "Unique" to filter the spill range results in 276 rows of spilled data. And that is likely to grow in my case. Is there any way to modify the DDL (I am not using the DDLSorter because my data is already presorted, so I just create the spill range directly from my table) to be used with more than 255 rows?

[@GeorgePapaioannou-n2m]

Is there any possible way to have it in online version?

[@jagtestar123abc]

Is it possible to make this solution, from a table who is always sorted, and from another sheet have a table with drop down dependency from the first table?

[@DataVisualisation]

... great solution! ... but too bad that the Unpivot DropDownList Challenge has come to a (super) end 😢 ... understanding the LAMBDA solution will be a headache for some 😮 ... but maybe there will be an explanatory video for those ...

[@esrAsnataS]

Is there any way to conditional format any errors? Similar to the other method Wyn used.

[@ExcelOffTheGrid]

Yes, 100% possible. Just use the range of values in the conditional formatting calculation.

[@cric8482]

Nice

[@jenniferlee4557]

Is there a way to easily store the lambda formula so it's usable in multiple workbooks or do we need to add it to the name manager in each unique workbook?

[@ExcelOffTheGrid]

They must be in the workbook so they travel with the workbook. We can create code to easily add them to workbooks at the click of a button.

[@digitalmigrate9532]

Is this available in the mastery course that you offer??

[@dominikskowron4904]

Thanks so much for DDL function. I'm struggling with crating a drop down list that appears only at certein condition: cell value. I think I lost all hope. Is there w way without VBA?

[@ExcelOffTheGrid]

I believe if you force an error for the source of the data validation list it will not display a list. Though it might display the drop-down button still. So I suggest you start there and see if it works.

[@dominikskowron4904]

Thanks for a quick answer. I does not solve my problem. I did not mention (my bad) that I want a value from another cell in that cell when the criteria for a drop down list in a cell are not me. So one scenario is, there is a value from "other cell" in my "main cell", second scenario: drop down list (when there is a scepific value in that other cell) in ty "main cell"

[@stanTrX]

Thank you. I suppose it keeos working even if you submit to online? For collabareted works. Another thing is, is it possible to hide your own custom functions like this if you share it?

[@stefankirst3234]

Is "%^&&@" just a random string that will most likely never occur in any data so that the textsplit won´t accidentally split the concatenated lookup values in a wrong spot?

[@ExcelOffTheGrid]

Yes, that is correct. It was a way of avoiding an additional delimiter argument.

[@RavneetSingh-rr6le]

Does comment automatically get remove if attach a link with it ??

[@DavidOnder]

Is there any way to combine DDL and DDLSorter into the data validation so then there would be no need for the intermediate table?

[@ExcelOffTheGrid]

Sorting converts a range into an array, and data validation lists don’t work with arrays. So, it needs the two stage process.

[@McIlravyInc]

This matches the concept o have for how ddls work. I still don't understand why the sort order has to be a limitation. But then i am the sort of person that chains adapters together to hook up an old device to a new one. Hahahaha Can't you copy the functions into the default workbook so every new file will have them?

[@YusufMumtaz]

You should license this to Microsoft.

[@renatovianello874]

{Mark,Wyn} = Excel Paramount

[@777kiya]

It's easiest I've ever seen. Does the formula DDL work on earlier versions of excel?

[@mramsch]

No, won't work as it depends on the LAMBDA function, first introduced in 2022 to Office 365 and recently to Excel 2024 (search for "What's new in Excel 2024 for Windows and Mac").

[@sscire]

Maybe I missed it in the presentation, ... but the three menus must contain the same number of items, each column ... or am I wrong?

[@ExcelOffTheGrid]

As it's a parent/child relationship between each column (e.g. the parent of any item in column 3 is in column 2, and the parent of any item in column 2 is in column 1), then they will contain the same number of items by their nature.

[@aliab2201]

❤❤❤

[@AdamMarks]

My company is still on an older version of excel that isn't automatically created a "UNIQUE()" set of values for the drop down. Is there a way adjust the formula to create just a unique listing?

[@ExcelOffTheGrid]

Unfortunately not - UNIQUE converts the range to an array - and the Data Validation list specifically requires a range. But at least you've got something to lookin forward to when the upgrade happens. 😁

[@McIlravyInc]

Hmmm.... can't you nest the array into another function to make a range? ...

[@ExcelOffTheGrid]

Nesting in a function doesn’t help. It has to exist in the face of the worksheet.

[@Henrik.Vestergaard]

Awesome - but is it just me: The dropdowns shows the expected values, but any value can be entered, which is not expected using data validation?

[@ExcelOffTheGrid]

Some of it depends on the settings applied, but even if you tighten up those, users can still work around it. We've got some options in this video here: kzbin.info/www/bejne/j5e0e2V4fax1gMk

[@Henrik.Vestergaard]

@@ExcelOffTheGrid Awesome link, thanks. Combining your ideas, I think I will go with INDIRECT(table[column]) to validate the first column (as this will prevent direct entering invalid values), the DDL on the following columns and then the conditional formatting to highlight invalid values 💪 Blocking copy-paste with VBA dont stop pasting from the neither the windows clipboard history nor the Excel clipboard history, and invalid values can still occur if the values in source for the validation is changes. I'm thankfull for your ideas 🙏

[@giovannigraziani80]

Subtitle please!

[@einoconsult5563]

Hi Mark, just tested in on Excel for Mac, and unfortunately this method does not work, on Windows obviously it works as you demonstrated it, but on Mac to my great sorrow, it does not work...I have sent the feedback to Microsoft

[@ExcelOffTheGrid]

It works on Excel Online and Windows. Unfortunately, it’s just another Mac difference to go along with hundreds of others.

[@katendefrancis6349]

Is this function only available to you? I cant seem to find it in my updated excel version

[@workstuff5253]

2 heads are better than 1

[@ciaucia156]

Classic blackbox - sorry

[@ExcelOffTheGrid]

It is a black box if you have access to all the code. And all the parts of the code are documented and explained in hundreds/thousands of publicly available articles and videos?

[@JoseAntonioMorato]

Dear Mark, I didn't like two things about this video: 1) The video didn't have subtitles and, as I don't speak English, I used the automatic translation into my language; 2) I found LAMBDA very complicated and I prefer the method shown in the previous video. 🤗

[@someguy2972]

Bet ya $50 you can't do a video with 5 dependent drop down lists.

[@ExcelOffTheGrid]

Why? What makes you say that? Is the function not working correctly?

[@someguy2972]

@@ExcelOffTheGrid Nah I just haven't seen someone do that many haha!

[@someguy2972]

@@ExcelOffTheGrid You'd probably be the first on youtube to do it (if it can be done of course). ;)

[@DanThorne54]

@@ExcelOffTheGrid I was able to make a 5 column dependent drop down list using this and an earlier tutorial! However, I'm stuck with what might be a drop-down list array record limit (details are in a reply off the root) that's only showing 10 of the 12 values that should appear in the first drop-down!