Well done - thanks. You nailed it - once you watch a few videos its easy to do the cascading lists ... for one row, but for multiple rows its a completely different challenge that catches you unawares
@Up4Excel6 ай бұрын
You're so right, it caught me unawares which is why I set about figuring it out and making this video. Thanks for your comment 👍 John
@Steve_Jobs_Excel5 ай бұрын
This was EXACLTY the video I was looking for, everyone else had their data sets formatted with the first drop down on the columns, and not per row. Now, to find out how to create 4 more columns of drop downs dependent on the previous choice while keeping all of the spill arrays in one single sheet :D Wish me luck !
@Up4Excel5 ай бұрын
Good luck 👍 It's worth checking the other comments as I know many others have added multiple extra dependants. In a nutshell though, adding more skilled lists to the right but leaving plenty of room between is the way to go. Thanks for watching and glad you found what you needed 👍 John
@OunissiMalak-yj5kq3 ай бұрын
Your videos are an absolute lifesaver! It saved me so much time and frustration. I can't thank you enough for sharing such clear and helpful instructions. Your expertise is truly a relief. Keep up the fantastic work!"
@Up4Excel3 ай бұрын
Wow, that's a lot of praise so thank you. Removing frustration and saving time is what I aim for 👍 John
@sakinaprimaswala337911 ай бұрын
After watching 10 videos , found the one i wanted. Thank you.
@Up4Excel10 ай бұрын
Glad I could help 👍 John
@MoodyDevta10 ай бұрын
This is the only video that actually solves my problem. Thank you so much!
@Up4Excel10 ай бұрын
Glad to hear it solved your problem. I made the video precisely because I couldn't find the solution anywhere so needed to invent it. It's great to hear loads of people like yourself are finding it useful 👍 John
@patrickschardt7724 Жыл бұрын
I have seen several of your data validation list videos. They have something unique about them and are very used in certain situations. This is the best one yet
@Up4Excel Жыл бұрын
Very glad to hear that Patrick. I'm sure I'm not the first to think of this idea but I've genuinely not seen it anywhere else. Hopefully I've brought something unique and useful to the community ☺️
@patrickschardt7724 Жыл бұрын
@@Up4Excel I’ve seen the idea of each cell having it’s own list that used helper columns for each cell. I’ve never seen it for dependent drop down lists. I’ve also never seen conditional formatting warnings ; it’s always VBA
@Up4Excel Жыл бұрын
@@patrickschardt7724 Double first hey! Thanks for watching and commenting Patrick
@peterkubs9 ай бұрын
Very useful way to control data. Thanks
@Up4Excel9 ай бұрын
My pleasure, glad you liked it 👍 John
@paramveerssachdeva3 ай бұрын
Loved your video. But, the music in the background should be softer or none at all. Just my feeling. Thanks for sharing such an amazing technique
@Up4Excel3 ай бұрын
Thanks for your feedback on the music. I've noticed it can be very different with or without headphones. I'd be interested in what sort of device you watched on and whether you had headphones so I can gauge volume in the future. Glad you found the technique useful btw 👍 John
@paramveerssachdeva3 ай бұрын
@@Up4Excel I had headphones on, and was watching it on my MacBook, but the music should definitely softer, maybe a piano playing laying a slow tempo music instrumental. Love your content. That's a 10 on 10. Am following and waiting to watch more videos. Have a great day.
@Up4Excel3 ай бұрын
@@paramveerssachdeva Listening back to this you're right about the volume, this one is definitely much louder than I put it these days....and with headphones on sounds even louder. I'll test all new releases with headphones in future, thanks for the feedback 👍John
@contextmatters82435 ай бұрын
Excellent, Excellent, Excellent! 🙂 I've been looking for just this answer for a couple days and kept running into roadblocks. 😞 One Question: Couldn't you put the columns you want to hide on a separate worksheet not giving anyone you may share the workbook with a chance to "screw things up"?
@Up4Excel5 ай бұрын
Very glad you like it and found your solution. Yes, you can put them on a separate sheet but the only issue might be keeping the rows in sync. For example, if you inset a row on the sheet with the drop downs you might come into problems if you don't insert a row in the same place on the other sheet. Give it a try though and let me know if you think of a work around for this 👍 John
@battle_born3 ай бұрын
Thank you sir! This made a colleague of mine veeeeery happy 😂😂
@Up4Excel3 ай бұрын
Glad to help your colleague out 👍 John
@raymondanoliefo Жыл бұрын
best video on KZbin. Thank you so much, helped me with my work at my Job.
@Up4Excel Жыл бұрын
Great to hear that Toby 👍 I was very happy when I worked this system out to solve a problem I had...glad you also found it helpful.
@grahamparker77297 ай бұрын
Great video, many thanks.
@Up4Excel7 ай бұрын
Glad you think so and thanks for saying 👍John
@vinayawagh6876 ай бұрын
Exactly what I was looking for! Thank you! 😁
@Up4Excel6 ай бұрын
Glad I could help 👍 John
@TheYadeThrrr7 ай бұрын
Thank you... I finally found what I was looking for..
@Up4Excel7 ай бұрын
Very glad you found me eventually and got what you needed 👍 John
@blancsoul7 ай бұрын
Thank you! You were really helpful!!!
@Up4Excel7 ай бұрын
Really glad to hear that, thanks for saying so 👍 John
@truewiking7 ай бұрын
This was brilliant, A lot of great tips here, Thank you very much 🙏
@Up4Excel7 ай бұрын
Glad you enjoyed it! Thanks for the comment 👍John
@truewiking6 ай бұрын
@@Up4Excel Just one comment on the spill list, there is a possibillity to have many since you hide them anyway so you can put them lower and lower in the sheet if needed. Anyways the basic setup here works like charm, many thanks
@Up4Excel6 ай бұрын
@@truewiking Very true you could do this...as long as you go far enough down so that your data is not going to extend in future and interfere 👍
@chrisgoes961011 ай бұрын
Very useful, many thanks for sharing
@Up4Excel11 ай бұрын
Glad it was helpful Chris 👍 John
@dougnyc83245 ай бұрын
I hope your channel gets more exposure from the KZbin algorithm, your Excel content is fantastic! Will do my small part by giving the like to each!
@Up4Excel5 ай бұрын
Great stuff Doug, thanks for your support and appreciate your likes👍 John
@hichamhadj96408 ай бұрын
The video is excellent. Have you considered placing the formula of the last column in a named range and utilizing it for data validation? This approach may potentially prevent the need for transposing.
@Up4Excel8 ай бұрын
I'd not thought of that but sounds promising and well worth a go. If you've got it working let me know. Thanks for the comment 👍 John
@arpansingha007 Жыл бұрын
Very helpful. Thank you very much
@Up4Excel Жыл бұрын
Glad it was helpful Arpan 👍
@ashokchudasama8588 Жыл бұрын
Excellent, Excellent and only Excellent .Great Respected Sirs.
@Up4Excel Жыл бұрын
Thanks you Ashok, happy to hear you say that 👍 John
@sirkitty0075 ай бұрын
This works great for me as I am using this as a daily production report for my work. I would like to know if there is a way to clear all drop down boxes choices. So each day start fresh data.
@sirkitty0075 ай бұрын
Never mind. Just saw the clear all contents on a right click menu I missed before.
@Up4Excel5 ай бұрын
Your can delete all the cell contents by highlighting all the drop downs and hitting the delete key...but do you want to automate that? If so, record it as a macro and run it each morning maybe??
@Up4Excel5 ай бұрын
Glad you got what you needed 👍 John
@sirkitty0075 ай бұрын
@@Up4Excel I did an active X box instead and added Range("D4:K23").Value = Nul Range("M4:M23").Value = Nul Range("C29:G48").Value = Nul Range("K29:K48").Value = Nul which erases all the data from the drop down boxes without removing formulas in the other cels
@Up4Excel5 ай бұрын
@@sirkitty007 Good stuff, glad you got it working 👍
@abeerattia452311 ай бұрын
Excellent video
@Up4Excel11 ай бұрын
Glad you think so. Hope it helped you 👍 John
@pelajarexcel11 ай бұрын
exactly what i looking for. Thank you. I subscribed
@Up4Excel11 ай бұрын
Thanks for the sub and comment. Glad you found it 👍 John
@KNamit Жыл бұрын
Such a useful video!
@Up4Excel Жыл бұрын
Glad you think so! 👍 John
@ryanmichael-o3m6 ай бұрын
Thanks for this it is what i was looking for. I love your accent too. Alos is there an easier way to do this i was trying to figure out how to do list for ordering at my tea shop i have individual and case weights so i implemented this for the category and product than have an if statement v lookup based on another category of single or case. am I overly complicating it or is this the best way to do it
@Up4Excel6 ай бұрын
If it's working then stick with it 👍 It doesn't sound especially complicated and you might spend a lot of time trying to perfect something different. Glad you found the video useful and my accent good 😀👍 John
@H4RI3 Жыл бұрын
Thank you very much for the solution to my problem. I was trying to solve it with ‘indirect’ (but that doesn’t give the result i expected. One question: Why not create the last columns on a different sheet instead of hiding them?
@Up4Excel Жыл бұрын
I never really thought about having them on a different sheet so potentially a good idea. I suppose you could then have multiple dependant drop-down each with there own sheet. There might be issues keeping rows aligned across sheets but generally sounds like a good idea to me. Thanks for sharing 👍
@H4RI3 Жыл бұрын
@@Up4Excel today i planned to test it out, but my chrome doesn’t want to upgrade for some reason so no 365 for me at this moment. Normally something i want to hide or don’t want to clutter my main sheet i make a separate sheet for it.
@Up4Excel Жыл бұрын
@@H4RI3 I think you're on to a good idea here so let me know if you get it working at some point 👍
@aldouse10 ай бұрын
Thanks! I like this as its a nicer/cleaner approach. but makes the rest of the cells useless/dead after the spill column.
@Up4Excel10 ай бұрын
Very true, but you can put the spilled column as far to the right as you want if you need more columns. Just keep inserting columns before the spilled column to add any more. John
@aldouse10 ай бұрын
@@Up4Excel thanks for the tip! didn't realize that was an option.
@Conradnoble8 ай бұрын
Thanks, you have made a very frustrating task for me actually feasible, by any chance is an omni-directional list possible? e.g. pick an item from list 3 before filtering list 2, and then having the list 2 results filtered by the list 3 choice?
@Up4Excel8 ай бұрын
Thanks so much for the support thanks Conrad and great to hear your frustration is gone 👍 As for omni-directional lists, you could do something with conditional formatting and helper columns to flag where they don't match but not sure there's a neat solution to it. Perhaps an IF statement on the spilled list might help by making it switch between list 2 and 3?
@diegograndon57195 ай бұрын
Excellent video, by far the best I've seen on the topic. I'm having a problem though and I need some help. In the source bit of the Data Validation I can't put something like "=$K4#" because I get an error, "This entry leads to an error". But if I use this "=$K$4#" there is no problem, but I need that removed in order to drag it down for each row. Help me please!
@Up4Excel5 ай бұрын
Have you downloaded the spreadsheet with video? If not have a look at that because I think it solves that issue. John
@CNixx44 Жыл бұрын
This has been extremely helpful. Thank you! But what happens if additional dependent columns are needed? For example, say you wanted to add a column between “Product Category” and “Product Name” called “Brand Name”. And you want “Brand Name” dependent on “Product Category”, but “Product Name” to be dependent on “Brand Name”. The arrays can’t all transpose to the right. Is there a solution for this?
@Up4Excel Жыл бұрын
If you know the maximum possible length of the arrays then you can have multiple arrays spilling to the right, just give them enough room. I've had many people tell me they've done this successfully and one even reporting getting 7 dependant columns working. Alternatively try using separate sheets for each array but sticking to the same row. I've not tried this but can't see why it wouldn't work. John. p.s. thanks for watching and commenting 👍
@redhaakhund127111 ай бұрын
Amazing 👍👍👍👍👍🙏
@Up4Excel10 ай бұрын
Thank you! Cheers 👍 John
@jurajminar632010 ай бұрын
Is there a way to make it work in sharepoint excel as well? Im trying to put in the # but it shows error. Is there any workaround?
@Up4Excel10 ай бұрын
Only to open it in the desktop version from SharePoint...assuming you also have the desktop version. It will sync with SharePoint but won't work whilst open in the browser version. Hopefully over time Excel online will build in all the features 👍 John
@wilybuffalo14356 ай бұрын
Is there a way to take this technique and add an "Other" option to the drop-down lists if the 'Other' doesn't appear in your data? I have a data set that is 8000 rows by 5 columns that I used this formula to create drop-down and dependent drop-down lists. Now, I've been asked to add an 'Other' option to those lists, however, the 'Other' isn't in the data and I would need to manually add about 16k additional rows to create the data set. Any shortcuts I don't know about? Thanks!
@Up4Excel6 ай бұрын
You could try using the HSTACK function to add 'Other' to every list. Basically embed the current formula inside HSTACK with "Other" as the next argument. If you want to make sure it only shows once combine the whole formula inside the UNIQUE function. Something like that might work 👍 John
@wilybuffalo14356 ай бұрын
@@Up4Excel I will try that! If my current formula is =TRANSPOSE(SORT(UNIQUE(FILTER(Table1[Manufacturer/Preferred Vendor],(Table1[Sub-Category]=$F5),"")))) Should I throw HSTACK at the beginning and then my formula, and then 'Other' and then close it all up?
@Up4Excel6 ай бұрын
Yes, exactly. I can't try it myself at present, but just play around and see what you get. Other needs to be in double quotes ( "Other" ) to mark as text.
@plummetplum6 ай бұрын
If i had a table of materials say with category, company, size, lengthy then price. How do i build a number of dropdown boxes side by side so eventually after all my selections a price is listed at the end? Ive not seen a tutorial to do that? Thx
@Up4Excel6 ай бұрын
You could link all the drop downs with the technique in this video. There are lots of comments from other viewers that have used this technique to link more than three lists. Have a look at those and my responses for ideas. Once you have all your drop-downs you could use the filter function to pick up the price based on all the other answers. SUMIFS would also work I suspect as long as there is only one price per set of options. Best of luck 👍 John
@aggiebq8611 ай бұрын
Is there a way to perform a manual override of the list? For instance if I have a master table with a product ID, Description and price but want to override or edit the description or unit price to account for one off items or a special discount.
@Up4Excel10 ай бұрын
There might be, but best practice would be to introduce an override column and then a final result column with a formula such as 'if override is blank then use list item, otherwise use override value'. Using this method would not only be technically simpler but also make any manual override very obvious. That's how I always handle it anyway. Thanks for watching 👍 John
@johnboffin4766 ай бұрын
How can I lock the Drop down menu width that it doesn't resize with other drop down next to it. (I know about column resize and all that, but I don't want drop down to increase it width by having another drop down menu next to it which is wider. Please help.
@Up4Excel6 ай бұрын
I'm not sure what you mean. These drop-downs are in cell s so match the column width. Can you clarify?
@johnboffin4766 ай бұрын
Yes you are right, but I have about 5 columns and 3 are same width and two are wider to fit about 25 letters, the rest is like 5 letters, I had resized the column width and when I do the wider columns data validation, the narrow one display wide drop down menu with 5 letters, soon as I don't do the wider drop down the narrow cells validation displays wider. I think I repeated few time. thanks.
@Up4Excel6 ай бұрын
I honestly don't know how to fix that. I've had lists gone up wider than cells myself but typically it isn't an issue. It sounds as if the drop list has some sort of memory for how wide it should be even when it could now be narrower. If you find a solution post it here as it's like to know. John
@johnboffin4766 ай бұрын
Thanks for your reply, I am on it and I will let you know soon as I find it how, thanks again.
@anshumansingh5379 Жыл бұрын
Sir how can we create dropdown list in which we have to put 2 or more values
@Up4Excel Жыл бұрын
Sounds like you need a checkbox rather than a drop down list. Perhaps look that up?
@danieljoetantobr11 ай бұрын
Damn, this is perfect, but I can't make it work on Google Sheets. It gives error all around.
@Up4Excel11 ай бұрын
When stuff starts getting advanced Google sheets and Excel start to diverge, so not surprised.
@RandyMcNeil2 Жыл бұрын
You seem to be making the assumption that your initial order list contains every possible combination of future orders, which makes selecting something new impossible.
@Up4Excel Жыл бұрын
I guess one solution would be to link the lists to an independent source containing every combination, assuming this is practical for the scenario. The method would be much the same though.
@Up4Excel28 күн бұрын
You're right, I am on this demo. You can take the lists from a full lookup list if you want though. The method would stay the same , just get the source lists from complete lists elsewhere on the spreadsheet.
@joseejrp96510 ай бұрын
UNIQUE DOES NOT WORK FOUND THIS A WASTE OF TIME
@Up4Excel10 ай бұрын
Unique function only works on Excel 365 like I say in the beginning of the video. It's well worth getting 365 as there's loads of extras in it and it's getting better all the time.
@amazdesignswithgabby71598 ай бұрын
Great video works wonderfully except when I try to copy the spill list down I am getting zeros and it shows the filtered list but the data validation box is empty.
@Up4Excel7 ай бұрын
Make sure you download the example spreadsheet. The link is in the description. You can then work out where your version is different and get it working 👍 John
@AdroRJ13 Жыл бұрын
This was exactly what I was looking for!!!! It was so perfectly explained, I could follow thru everything even though English isn’t my first language 🫶🏻 Thank you so much ❤
@Up4Excel Жыл бұрын
You're very welcome. I'm happy to hear it was easy to follow, especially as it is a fairly advanced technique. Thanks for commenting 👍John