Excel Dynamic Multi-Dependent Dropdowns on Every Row - Best User Experience
Vložit
- čas přidán 18. 08. 2024
- 📗 Get your free template here: »» cutt.ly/up4v21...
🎯 You may think you know how to make dependent dropdown lists….but only this technique allows you to have them on every row! Make data entry a breeze with unique dropdown lists on every row with multiple dependencies too.
======================================================================================
🕑 Video Timings:
00:00 Unique Lists on EVERY Row
01:50 Built In Error Checks
02:50 The Secret Helper Cells
03:57 Starting From Scratch
04:55 Make Life Simple with Tables
06:02 Base List Creation - Easy Method
08:14 Region Dropdown List
09:41 Every Row Dropdown Shortcut
10:50 Dependent List - Step 1
13:22 Dependent List - Step 2
14:35 Add Extra Dependent Criteria
16:52 Work Around Errors & Issues
20:17 Add Product Check Column
23:58 Add Product Check Highlight
25:35 Conceal Workings for Users
26:32 Downloads Library Access
======================================================================================
📖 look at this spreadsheet right we've got product names being entered and we've got drop down lists to pick from but look right this drop down list if i change this product category here we've got a completely different drop down list and not only that every row is different and not only that these two the same product category but they've got a different list on each just because we've got a different region as well every single line has got a completely different list to pick from a seamless user experience you never know what's gone into this and you may be thinking well i know how to do dependent drop downs and of course you'd be right but 99 of the time what you'll find you know as with me is that i knew how to do dependent drop downs and cascading drop downs when i just wanted one set of outputs i didn't necessarily need completely different criteria on every single line because there's a lot of information out there about how to do dependent drop-downs but typically it involves big separate calculations on the second or subsequent drop down and you just don't have the luxury of that when you've got to do something different on every single line so there's no vba no code in this you can grab this spreadsheet right now click the link in the description completely free no details needed and work along i'll show you exactly how you can use that template and create this thing completely from scratch for yourself
one little caveat is that you're going to need to be using excel 365 or excel 2021 onwards to make use of the formulas for this technique so if that's you excellent download get on with it we'll crack on just want to demonstrate before i show you this one other feature because you might have thought well let's say i picked that copier there for um this region but then i change this to like say envelopes there you go i was about to say well you don't want a copier to be part of an envelope category and it's not it's flagging it up so it's also going to flag up where you have errors and now the drop down list has changed to represent the envelopes available in that region so you've got a bit of semi intelligence and bit of error checking built into this too and of course i'm going to show you how you do that as well
the big reveal okay let's unhide some of these hidden rows and columns here so let's unhide these rows first and you can see we've got a couple of extra columns we've got a product check and that's what bringing that conditional format into play
======================================================================================
#Up4Excel
#Up4ExcelControls
#Up4ExcelIntermediate
Here at Up4Excel we’re on a mission to help YOU:
» Get your Excel skills UP and your task time DOWN
» Focus on shortcuts and fast impressive results
» Improve your productivity and free up your time
Everyone will assume you work 24 hours a day to produce the kind of output you’ll be producing in no time…. with the help of Up4Excel training.
You get new video releases every week, packed full of ways to save time and impress those around you.
💎 Don't miss out and fall behind.....
🅾 SUBSCRIBE NOW 🅾
cutt.ly/Up4Exc...
======================================================================================
👀 Learn more with my playlist: Using Drop Down Lists in Excel
»» • Using Drop Down Lists ...
💥 Get a Shortcuts Cheat Sheet: »» www.up4excel.co...
🔑 Remove Excel File Open Passwords: »» • Excel File Locked? Lea...
🎁 Feel free to donate - your small gift will help me make better videos for you and others.
Thank You: www.paypal.me/...
👉 Get ALL Up4Excel workbooks and templates on YOUR device in a structured searchable index….with free lifetime updates
📕 Up4Excel Downloads Pack: »» ml.up4excel.co...
Wow, after hours of intense research, I finally found the solution I was looking for in this video! 🙌 Huge thanks to the creator for sharing this helpful fix. You're a lifesaver! 💯
Great to hear that and thanks for saying so. 👍 John
Perfect solution for my project! Thank you so much😊
Great to hear, thanks for letting me know 👍 John
You are the first person to solve my issue correctly - well done and thank you. Keep up the excellent work!
Glad to hear I helped you solve it Kevin. Thanks for commenting 👍 John
Oh man, you are the first that talk about transpose in dynamic multi dependent drop down list. I really, really appreciate that. You don't know how many days I was trying to solve my multi dependant brand list with just added a little formula of transpose. That little thing make so much different.
So glad you found my tips useful. It often is just a little piece of the puzzle that solves the whole thing for you like that. 👍 John
Good job, nice explined
John, why indeed a whole year? I think you’ve known all along why it took over a year. You made the statement, “You probably think you know how to do drop-down list, and you probably do.” The point is people saw “drop-down lists” and moved on not knowing what they were missing. I was completely blown away when I watched the video. You are truly a connoisseur of excel. Well done!
That's some of the best praise I've ever had Donald so thanks so much. It's the video I'm personally the most proud of yet, and I'm really glad to see it taking off and helping people like yourself 👍 John
Just want to thank you for this video because I have been searching for about 2 days for this. My spreadsheet has more than one column that has dependencies so I was able to apply the same methodology twice, so that the drop-down in cell B1 relies on cell A1, and the drop-down in cell C1 relies on B1. Worked great and works down the rows.
That's great Melissa. Thanks for taking the time to comment. It's always good to hear when one of my videos helps someone 👍
wow, bravo, this is amazing !! thank you 🙂
Glad you think so, and thanks for saying so 👍 John
This is amazing! THANK YOU. I've been trying to figure this out forever.
Glad to help Danielle. Thanks for saying so 👍 John
Words cannot emphasize how much I thank you for this.
That's high praise indeed Juan. Thanks very much, glad to help 👍
Wow, so much simpler than slicers and pivots.
Please outline which parts don't work in older excel versions.
Thanks again, subscription earned!
You're welcome, glad you found me! Anything that uses spilled/dynamic arrays only works in Excel 2021 or Excel 365 onwards....but that is the main technique so essential the whole system needs those Excel versions 👍John
@@Up4Excel i found "extract" sort of works in 2007 to get the basic list, but you can't do anything with it in the validation.
Thanks again.
Thank you Master
You are very welcome...plus thanks for the master comment 👍 John
Thank you very much teacher. God damn it, there is no place I haven't searched on youtube for a week, I'm looking for it, I'm very, very grateful to you. Please, continue with informative and instructive content. Greetings from Turkey.
Hello in Turkey. Very glad to have provided a solution for you. I shall certainly continue with my content 👍
Brilliant!!!!
Thanks!!
Glad you think so. Thanks for saying 👍 John
This video was very helpful and helped me to complete my task that i took charge of.
Excellent, glad it helped 👍 John
This is exactly what I've been looking for! Thank you!!!
Glad to hear that Shelly. Thanks for leaving a comment 👍 John
I have to do lists of room designation and usage (and floor area) for all our buildings at work and if i create a blank template with these drop downs it will save so much time. Brilliant! Thanks John.
That's great. I'm really pleased to hear you have a use case that will save you lots of time 👍
great video! waiting for the day you can use array functions directly for drop-down-list!
That will certainly be useful app I don't expect it'll be too long before they add it in 👍 John
Thanks a lot! It took me quite some time to find this explanation... Great video
Thanks Mike. I had to work it out for myself as I couldn't find anything online. Glad to help you 👍 John
Thankyou so much Sir, it saved me in my workspace in a particular database management tool.. i tried to replicate the same, it works beautiful, my day is saved.. thankyou so much. Please continue more.
Wow, very glad to help you so much and you got it working well 👍
Best excel teacher ever! This was extremely useful, I can´t thank you enough!
Thank you too for such excellent praise. Glad to hear you found it so useful 👍
awesome stuff, for right value validation I used instead =OR(G2="",G2=AF2#)
Good idea and nice concise formula Bubba. Thanks for sharing 👍
Great approach! Thank you so much for doing this video.
Thanks for the feedback Luis. Glad you enjoyed it 👍
Thank you for the awesome trick
Can we achieve this inside of an excel table?
Unfortunately not because tables don't allow spilled lists at present. You might be able to do it if you keep the spilled lists outside the table, but unlikely to give you much benefit. Thanks for watching and glad you liked the video 👍 John
Super helpful... Thanks!
Glad it was helpful 👍
Just a quick note to say "Thank you so much" for this video. You efforts helped me a great deal in trying to figure this out. ChatGPT just wasn't doing it. Still wish you didn't have to do the data transformation as you mentioned. Maybe excel will get there. Have you ever tried to keep all your lists and spills in a total separate file and have your working file reference them? And if so, did it work / any watch outs? Thinking of keeping this data in a separate file on a shared drive to save space on the actual excel "data entry" file. Again, thank you so much for sharing this with the world!
Thank you for your awesome praise there 👍 I've never tried the separate file method you suggest but there's no reason you couldn't use separate sheets to store the spilled arrays. You could potentially hide those sheets too to clean things up. Even if you could get a separate file working, it would carry risks in that it would always need to be open at the same time and kept in sync. Thanks again for your comments👍 John
Absolute Legend!
Glad you think so 👍 John
This is great stuff! Thank you so much. I managed to complete all of this, but I also want to have the option for a multi-selection in the drop-down menu. How would you do that? Say you want to have two regions in your selection and give you the drop-down list of product categories in BOTH of the two regions? Thank you in advance.
Glad you liked the video. To combine items like you suggest you might be able to make use of the FILTER function and add criteria together to create OR logic. I.e. in this region OR in this other region. Maybe try looking into that. 👍 John
Awesome
Glad you think so 👍 John
This is great, thanks very much! Is there an easy way to add in additional dynamic dependant dropdowns after this column?
Interesting question this actually Charlie, as the final list is a spilled range that could, in theory, extend to any amount of columns. However, if you know the previous to last list has a maximum length you could leave enough space for another spilled list of values for a fourth drop down.
@@Up4Excel thanks alot! I will see how I get on :)
Thought I'd update and thank you again! I have managed to get this working on 7 consecutive columns, you are the man!
@@CharlieMac. Excellent stuff. Certainly pushing things further there Charlie. Glad it worked 👍
Thank you so much for this video! I'm having an issue with 365 online not letting me remove the row number absolute in the validation box. If $L$4# is in there the dropdown works fine, but if $L4# or L4# is in there it says "This entry leads to an error". Do you know what I'm doing wrong and how to fix it? Could it be I need to use the app, as the online version has limited features? Thank you.
Edit: It is because I was using the online version. Since changing over to the desktop app this works great! Thank you again!
Thank you for commenting and I'm glad you worked out the issue. It's a shame but the online version of Excel doesn't support many of the desktop app features, maybe over time it will. 👍 John
LET ME SAY IT'S FABILOUS SIR
Well thank you very much indeed. I'm glad you think so 👍 John
It would be super helpful if we could see the tabs (sheets) that you're on. I've spent two days without any success on how to achieve this. I've mapped each of your steps and got different results.
There's a free download with the video all set up ready to use. The link is in the description, no email required or anything. Make sure you get this and you should be able to follow along.
Great technique - thanks for the explanation. It's such a shame that we can't (yet?!) use dynamic functions in Data Validation or tables. It means we lose a lot of the benefits of tables and have the danger of bloated workbooks if we have to put in formatting in lots of empty rows. Back to VBA to get around this!
I agree Shirley, hopefully they will integrate dynamic arrays into a lot more parts of Excel in future. I use conditional formatting on dynamic array spills to make them look like tables sometimes...avoids blank rows being formatted 👍 John
Can you do a tutorial for Google Sheets please!
If love to say yes but for the foreseeable future I'm sticking to Excel only.... There's so much more I want to share for Excel before I branch out.
This was good if you have a small number of columns, but I need to create one where column A is a drop-down using Data Validation, but then column B is dependent on A, then C is dependant on B and so on for 7 columns
Assuming you need these dependencies to be unique for each row you can still use this technique. Just give yourself enough columns for the spilled data of each option. You can have thousands of columns in Excel so it would only be an issue if there are 100s of choices for each option.
Note if you don't need each row to have unique dependencies then use standard dependent drop-down techniques like this: czcams.com/video/g41VIvQxUfk/video.html
Hello. This video was very useful! :) This works if the fields are filled with words but it doesn´t work if in the fields we have numbers. There is any solution to overcome this problem?
Glad you found the video useful. If you just want to restrict to numbers or even a particular range if numbers then standard data validation options will let you do that. If it is a list of exact numbers then use the video method 👍 John
It worked!!!!!! 😁 Thank you so much!!!
@@anafernandes46Good to hear 👍 John
That is the outcome I need but still can`t get even upon watching your video. Interesting but futile after all - depndent dropdown list is not working so far:(
Make sure you download the free example template.... Link in the description. Hopefully with that you can get things working for you 👍 John
great video thanks for sharing, but looks like it would not work if the Input sheet has a table rather than a range.
I have similar problem but all solutions available on CZcams will not work if data validation is being applied inside a table.
You are quite correct. One downside of tables is they can't handle spilled arrays, and as this technique relies on them it can't be used on tables. I don't know any way around this either.
If you find one of love to hear it though so please share. Good luck.
@@Up4Excel I have found a way to make this work - it involves creating some concatenated/TEXTJOIN helper columns (using underscore to join) in the original table, and creating additional lookup tables to map the helper columns to the next level.
If we have three levels of dependent drop downs, the helper column is Level1_Level2.
The lookup table contains two columns - the first being all combinations of Level1_Level2, and the second being the matching Level3 options (this can be created from the fully mapped table using Power Query, or having a dynamic range using UNIQUE.
Named ranges are assigned to the headings of the Lookup table, and to the data in each column
Once Level1 and Level2 have been selected, Level3 uses data validation using
=(OFFSET(Level1_Heading,MATCH($G2,Level1Data,0),,COUNTIF(Leve1Data,$G2),))
G2 is where the Level1_Level2 helper column is situated in the datainput table.
Hope that sort of makes sense but do get in touch if you'd like a better explanation
I am so stuck on this. I have 3 columns referencing a hierarchy, Parent, Child and Grandchild parts. When I pick Parent category, I need to have child category choices, thus when I picking child I need to see grandchild category choices. I can't quite get things to work. First column was simple I have my unique list of choices. I can't seem to get my child to work with my parent and grandchild to work with my child. (tears). :)
It can be tricky to set up. Make sure you download the free example workbook and go through that. The link's in the video description.
In theory your grandchild to child list should be identical in form to the first list, but with all your links changed. Getting the list filtered is often the tricky bit, and every setup is different. Hopefully actually working through the example workbook will prompt some ideas for you 👍 John
I can't get the last raw using #, it's not working. what is the reason for that?
Are you using the very latest version of Excel? The # function is quite new really.
Hey This is very helpful, but for some reason this is making the file work slow. I had earlier used offset function to arrive at the dependent drop down and it kept calculating threads and made the file slow. I then came across your video and remade the file as per this video but the excel speed isn't catching up. Can you please help?
I have a couple of suggestions. First, check the last cell with CTRL+END and make sure your file isn't saving a while load of empty cells. If it is you'll need to delete all the empty rows and columns and save the file. Second idea is recreate the file in a brand new spreadsheet as your file might be corrupted. Hopefully one of them will work.
This is exactly what I need! but I can’t get it to work. At about 12mins in of your vid (where we are applying FILTER) I’m following things step by step but getting this error ‘This value doesn’t match the a validation restrictions defined for this cell’ any ideas how to fix?
Sounds like you have data validation set for that cell, and probably others too. Clear the data validation (search up that as pretty easy) and it should be ok after. Good luck 👍
@@Up4Excel thanks so much for your quick reply!
So I cleared all the original lists that i had set up as per the first part of your video - except in my first column
And it seems to populate data in my other column but there’s now no drop down arrow as the data validation - list was removed ahhh this is so hard 😂 can I pay you to help me set it up? I have 4 columns, each need to be dependent on the data in the column selected before it
@@billiethepom1660 As far as paying me to do it goes, I'm more about teaching you to fish than catching the fish for you Billie 😉, but if you're interested in training with me have a look at this ml.up4excel.com/etss . You may be able to get help from @Charlie Maclean who commented on this video he has a system working on 7 consecutive columns!
What is the Substitute of "Filter Function" , since I have don't have office 365???
I'm pretty sure there isn't one. But if there is it won't spill out values across multiple cells like FILTER so can't be used for the technique in this video. You might be able to utilise an old school CTRL ALT ENTER array function but that's a long shot and I wouldn't know how to go about it. Sorry can't be more help but I think Excel 365 is the way to go, and it's getting better all the time too.
@@Up4Excel Thanks a lot!
which version you are using here ?? As i am not getting unique and sort in 2016 version
Excel 365. It's always updated with the latest features and well worth getting.
The music on the background is awful. Please, never put the background like this when you areate a video that is going to give the users so useful info that is on high demand.
That's good feedback Holy. You're the first person to ever comment on the music on any of my videos. I typically keep it very quiet and only play it on intros and endings but it'd be interesting to hear what others think too. Is it the particular music you think is awful or just the fact there is some? John
@@Up4ExcelMusic itself is ok. It has nothing to do with the music style. It's all about it's volume level. It was really distracting from what you're saying. If you show something new, people need to focus on the details and be able to catch all of them. When music interupts your explanation it irritates. Sorry, if it's too direct, but I prefer to call a spade a spade so that people are able to understand and make the right decision.
@@Holy_Random I'm glad you are willing to call a spade a spade and welcome your comments Holy. I've played this one back and admit the music seems louder than I normally have it, and perhaps the voice is also a bit more distant too? I edit most of my own videos but this one went to an external editor. It would be interesting to hear if volume is still an issue in some more recent videos I edited myself such as this czcams.com/video/n0WYT2rBihg/video.htmlsi=DvUO9eOOVf4ChMsw ....Note the intro music volume is high but then I turn it low as I get into the content. I'd love to hear your thoughts 👍John