Excel Dynamic Multi-Dependent Dropdowns on Every Row

Excel Dynamic Multi-Dependent Dropdowns on Every Row - Best User Experience

Рет қаралды 22,333

Күн бұрын

📗 Get your free template here: »» cutt.ly/up4v2112M01FD
🎯 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/Up4ExcelSub
======================================================================================
👀 Learn more with my playlist: Using Drop Down Lists in Excel
»» • Using Drop Down Lists ...
💥 Get a Shortcuts Cheat Sheet: »» www.up4excel.co.uk/shortcuts
🔑 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/Up4Excel
👉 Get ALL Up4Excel workbooks and templates on YOUR device in a structured searchable index….with free lifetime updates
📕 Up4Excel Downloads Pack: »» ml.up4excel.com/downloads_pack

Пікірлер: 102

@gauravm201 7 ай бұрын

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! 💯

@Up4Excel 7 ай бұрын

Great to hear that and thanks for saying so. 👍 John

@shelley3622 11 ай бұрын

This is exactly what I've been looking for! Thank you!!!

@Up4Excel 11 ай бұрын

Glad to hear that Shelly. Thanks for leaving a comment 👍 John

@kevinsteenkamp 9 ай бұрын

You are the first person to solve my issue correctly - well done and thank you. Keep up the excellent work!

@Up4Excel 9 ай бұрын

Glad to hear I helped you solve it Kevin. Thanks for commenting 👍 John

@eav4528 Жыл бұрын

Best excel teacher ever! This was extremely useful, I can´t thank you enough!

@Up4Excel Жыл бұрын

Thank you too for such excellent praise. Glad to hear you found it so useful 👍

@senacool13 9 ай бұрын

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.

@Up4Excel 9 ай бұрын

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

@juanlopezmartinolich7882 2 жыл бұрын

Words cannot emphasize how much I thank you for this.

@Up4Excel 2 жыл бұрын

That's high praise indeed Juan. Thanks very much, glad to help 👍

@daniellemartineau4065 8 ай бұрын

This is amazing! THANK YOU. I've been trying to figure this out forever.

@Up4Excel 8 ай бұрын

Glad to help Danielle. Thanks for saying so 👍 John

@LuisGarcia-mn4jg 11 ай бұрын

Great approach! Thank you so much for doing this video.

@Up4Excel 11 ай бұрын

Thanks for the feedback Luis. Glad you enjoyed it 👍

@wanrusilawatiwanrahin3994 28 күн бұрын

Perfect solution for my project! Thank you so much😊

@Up4Excel 28 күн бұрын

Great to hear, thanks for letting me know 👍 John

@user-iu8bg5yt8v 11 ай бұрын

Thanks a lot! It took me quite some time to find this explanation... Great video

@Up4Excel 11 ай бұрын

Thanks Mike. I had to work it out for myself as I couldn't find anything online. Glad to help you 👍 John

@hanlardanmete9512 Жыл бұрын

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.

@Up4Excel Жыл бұрын

Hello in Turkey. Very glad to have provided a solution for you. I shall certainly continue with my content 👍

@cycleSCUBA 2 жыл бұрын

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.

@Up4Excel 2 жыл бұрын

That's great. I'm really pleased to hear you have a use case that will save you lots of time 👍

@majorsundown8453 Жыл бұрын

Super helpful... Thanks!

@Up4Excel Жыл бұрын

Glad it was helpful 👍

@jpcrtube 2 ай бұрын

wow, bravo, this is amazing !! thank you 🙂

@Up4Excel 2 ай бұрын

Glad you think so, and thanks for saying so 👍 John

@MrRoryellie 2 ай бұрын

This video was very helpful and helped me to complete my task that i took charge of.

@Up4Excel 2 ай бұрын

Excellent, glad it helped 👍 John

@heribertoquintanilla743 9 ай бұрын

Brilliant!!!! Thanks!!

@Up4Excel 9 ай бұрын

Glad you think so. Thanks for saying 👍 John

@donalddeno9268 11 ай бұрын

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!

@Up4Excel 10 ай бұрын

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

@tacobellemel Жыл бұрын

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.

@Up4Excel Жыл бұрын

That's great Melissa. Thanks for taking the time to comment. It's always good to hear when one of my videos helps someone 👍

@billalzeeshaan 7 ай бұрын

Thank you Master

@Up4Excel 7 ай бұрын

You are very welcome...plus thanks for the master comment 👍 John

@thulanincube4817 10 ай бұрын

Absolute Legend!

@Up4Excel 10 ай бұрын

Glad you think so 👍 John

@MishkyGammy Жыл бұрын

awesome stuff, for right value validation I used instead =OR(G2="",G2=AF2#)

@Up4Excel Жыл бұрын

Good idea and nice concise formula Bubba. Thanks for sharing 👍

@Energy_Agent Жыл бұрын

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.

@Up4Excel Жыл бұрын

Wow, very glad to help you so much and you got it working well 👍

@2001pulsar 4 ай бұрын

Wow, so much simpler than slicers and pivots. Please outline which parts don't work in older excel versions. Thanks again, subscription earned!

@Up4Excel 4 ай бұрын

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

@2001pulsar 4 ай бұрын

@@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.

@jeremy_317 7 ай бұрын

great video! waiting for the day you can use array functions directly for drop-down-list!

@Up4Excel 7 ай бұрын

That will certainly be useful app I don't expect it'll be too long before they add it in 👍 John

@Capsic66 8 ай бұрын

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!

@Up4Excel 8 ай бұрын

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

@reanalytics1863 3 ай бұрын

Thank you for the awesome trick Can we achieve this inside of an excel table?

@Up4Excel 3 ай бұрын

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

@dpcomm 9 ай бұрын

Awesome

@Up4Excel 9 ай бұрын

Glad you think so 👍 John

@Aeradota2 8 ай бұрын

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.

@Up4Excel 7 ай бұрын

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

@CharlieMac. Жыл бұрын

This is great, thanks very much! Is there an easy way to add in additional dynamic dependant dropdowns after this column?

@Up4Excel Жыл бұрын

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.

@CharlieMac. Жыл бұрын

@@Up4Excel thanks alot! I will see how I get on :)

@CharlieMac. Жыл бұрын

Thought I'd update and thank you again! I have managed to get this working on 7 consecutive columns, you are the man!

@Up4Excel Жыл бұрын

@@CharlieMac. Excellent stuff. Certainly pushing things further there Charlie. Glad it worked 👍

@tiffanydrouin2622 7 ай бұрын

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!

@Up4Excel 6 ай бұрын

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

@user-tn8ud6kc1n Жыл бұрын

which version you are using here ?? As i am not getting unique and sort in 2016 version

@Up4Excel Жыл бұрын

Excel 365. It's always updated with the latest features and well worth getting.

@menupp1269 Жыл бұрын

Can you do a tutorial for Google Sheets please!

@Up4Excel Жыл бұрын

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.

@dailycooper 8 ай бұрын

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.

@Up4Excel 8 ай бұрын

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.

@shirleymoreman6725 10 ай бұрын

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!

@Up4Excel 10 ай бұрын

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

@samthajain2417 Жыл бұрын

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?

@Up4Excel Жыл бұрын

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.

@IbrahimNajjar-by2dk Жыл бұрын

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 KZfaq will not work if data validation is being applied inside a table.

@Up4Excel Жыл бұрын

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.

@lovegroova 11 ай бұрын

@@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

@ABellaLuna Жыл бұрын

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

@Up4Excel Жыл бұрын

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: kzfaq.info/get/bejne/nZphiayntt65lp8.html

@anafernandes46 2 ай бұрын

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?

@Up4Excel 2 ай бұрын

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

@anafernandes46 2 ай бұрын

It worked!!!!!! 😁 Thank you so much!!!

@Up4Excel 2 ай бұрын

@@anafernandes46Good to hear 👍 John

@billiethepom1660 Жыл бұрын

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?

@Up4Excel Жыл бұрын

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 👍

@billiethepom1660 Жыл бұрын

@@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

@Up4Excel Жыл бұрын

@@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!

@mahrezchabane6895 11 ай бұрын

LET ME SAY IT'S FABILOUS SIR

@Up4Excel 11 ай бұрын

Well thank you very much indeed. I'm glad you think so 👍 John

@kavinduvishwajith1068 Жыл бұрын

I can't get the last raw using #, it's not working. what is the reason for that?

@Up4Excel Жыл бұрын

Are you using the very latest version of Excel? The # function is quite new really.

@user-xs4us2zl6d Жыл бұрын

What is the Substitute of "Filter Function" , since I have don't have office 365???

@Up4Excel Жыл бұрын

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.

@user-xs4us2zl6d Жыл бұрын

@@Up4Excel Thanks a lot!

@svitovidable 4 ай бұрын

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:(

@Up4Excel 4 ай бұрын

Make sure you download the free example template.... Link in the description. Hopefully with that you can get things working for you 👍 John

@labigator 8 ай бұрын

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). :)

@Up4Excel 8 ай бұрын

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

@Holy_Random 8 ай бұрын

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.

@Up4Excel 8 ай бұрын

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

@Holy_Random 8 ай бұрын

@@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.

@Up4Excel 8 ай бұрын

@@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 kzfaq.info/get/bejne/pJaHjLdj16jNmJs.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