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.
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!!
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 :)
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.
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?
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.
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.
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?
... 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.
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) ?
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?
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 ❤
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.
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?
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??
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?
... 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 ...
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?
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.
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?
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?
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. 😁
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").
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.
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?
@@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!
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. 🤗
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.
wyn and yourself are really a couple of excel experts. thanks for all of your hardwork!
Cheers Colin 😀
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!!
Wow, thank you! That is very kind of you to say. 😁
Very cool direction for solving the challenge of dependent drop downs. Really like it!
Thanks, I’m glad you liked it. 😁
Excellent Excel off the grid. Job made easier!. Thanks for your hard work.
You are welcome! I hope you can put it to good use.
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 :)
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.
Love you guys! Super tip!
Easy to use? YES! - Easy to understand? NO 🤯 - Thanks for sharing this!
And we have a winner! 🎉🎉💥🌟✨️❤
Team work makes the dream work 😁
Woaw, that's another level trick, really liked it.
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?
Yes there is - see this: Don't trust data validation in Excel! | Excel Off The Grid
ua-cam.com/video/YfQJ2BHGBJg/v-deo.html
Wow! this is amazing! Thanks for sharing it. Love it.
Nice. SVery nice indeed. Thanks for your hard work, creativity and sharing.
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.
Excellent..🎉 easier than ever... 🎉 thanks for sharing 😊
🔥🔥🔥
Great solution, 👏
Mark absolutely in Magic mode! Don't miss it. Enjoy!!! Thanks for the early Christmas gifts...
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.
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?
... 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.
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) ?
you're amazing ❤😊
just WAAAAUW!
Thanks 😁
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?
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 ❤
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.
That's quality.
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?
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.
Is this available in the mastery course that you offer??
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??
Does comment automatically get remove if attach a link with it ??
Is this function only available to you? I cant seem to find it in my updated excel version
Nice
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?
... 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 ...
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?
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.
I will wait for 3 months when you come up wtih an even better solution. 🙂
Or wait 10 years before Microsoft create a new formula for just this
You should license this to Microsoft.
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?
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?
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. 😁
Hmmm.... can't you nest the array into another function to make a range? ...
Nesting in a function doesn’t help. It has to exist in the face of the worksheet.
❤❤❤
It's easiest I've ever seen. Does the formula DDL work on earlier versions of excel?
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").
{Mark,Wyn} = Excel Paramount
Maybe I missed it in the presentation, ... but the three menus must contain the same number of items, each column ... or am I wrong?
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.
2 heads are better than 1
Subtitle please!
Classic blackbox - sorry
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?
Bet ya $50 you can't do a video with 5 dependent drop down lists.
Why? What makes you say that? Is the function not working correctly?
@@ExcelOffTheGrid Nah I just haven't seen someone do that many haha!
@@ExcelOffTheGrid You'd probably be the first on youtube to do it (if it can be done of course). ;)
@@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!
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. 🤗