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
Clear explanation, great video I have been searching for a way to have dependent lists based on filter, unique functions. Fortunately , I made it work with a slight help from VBA worksheet change event. The method in this video is more efficient. Thanks for sharing
Thanks for the comment. Took me a while to come up with this method so glad you appreciate it. Like you I'd been using VBA for a while to do this sort of thing but get the feeling it's on its way out so wanted something every user could access 👍 John
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
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 ☺️
@@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
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!"
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
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 !
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
@@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
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"?
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
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.
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??
@@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
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
@@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.
@@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
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.
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!
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
@@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?
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.
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
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
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?
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?
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
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!
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?
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 👍
@@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.
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
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
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
Unfortunately there isn't one, in this context anyway. Excel 2019 doesn't have dynamic arrays and that means it can't spill arrays of answers across cells as this solution requires. You can kind of replicate a SORT function in Excel 2019 using RANK and then INDEX + MATCH. I have a video doing this for items in dropdowns if you're interested...it's here: ua-cam.com/video/j5n2faFrvWs/v-deo.html
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 ❤
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?
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 👍
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.
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
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.
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.
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
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.
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.
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.
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.
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.
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
One od the best videos i had ever seen on drop. Down list. Great teacher ❤
Wow, thank you! That's some compliment 👍John
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
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
I was in search of the. Same. Thanks alot you. Create this.
Clear explanation, great video
I have been searching for a way to have dependent lists based on filter, unique functions.
Fortunately , I made it work with a slight help from VBA worksheet change event.
The method in this video is more efficient.
Thanks for sharing
Thanks for the comment. Took me a while to come up with this method so glad you appreciate it. Like you I'd been using VBA for a while to do this sort of thing but get the feeling it's on its way out so wanted something every user could access 👍 John
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
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 ☺️
@@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
@@patrickschardt7724 Double first hey! Thanks for watching and commenting Patrick
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!"
Wow, that's a lot of praise so thank you. Removing frustration and saving time is what I aim for 👍 John
After watching 10 videos , found the one i wanted. Thank you.
Glad I could help 👍 John
This is the only video that actually solves my problem. Thank you so much!
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
Exactly what I was looking for! Thank you! 😁
Glad I could help 👍 John
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 !
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
Very useful way to control data. Thanks
My pleasure, glad you liked it 👍 John
Great video, many thanks.
Glad you think so and thanks for saying 👍John
Thank you sir! This made a colleague of mine veeeeery happy 😂😂
Glad to help your colleague out 👍 John
best video on UA-cam. Thank you so much, helped me with my work at my Job.
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.
Thank you... I finally found what I was looking for..
Very glad you found me eventually and got what you needed 👍 John
This was brilliant, A lot of great tips here, Thank you very much 🙏
Glad you enjoyed it! Thanks for the comment 👍John
@@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
@@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 👍
Thank you! You were really helpful!!!
Really glad to hear that, thanks for saying so 👍 John
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"?
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
Very useful, many thanks for sharing
Glad it was helpful Chris 👍 John
I hope your channel gets more exposure from the UA-cam algorithm, your Excel content is fantastic! Will do my small part by giving the like to each!
Great stuff Doug, thanks for your support and appreciate your likes👍 John
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.
Never mind. Just saw the clear all contents on a right click menu I missed before.
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??
Glad you got what you needed 👍 John
@@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
@@sirkitty007 Good stuff, glad you got it working 👍
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
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
@@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.
@@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
Excellent video
Glad you think so. Hope it helped you 👍 John
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.
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
exactly what i looking for. Thank you. I subscribed
Thanks for the sub and comment. Glad you found it 👍 John
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!
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
@@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?
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.
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
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
Very helpful. Thank you very much
Glad it was helpful Arpan 👍
Excellent, Excellent and only Excellent .Great Respected Sirs.
Thanks you Ashok, happy to hear you say that 👍 John
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?
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?
Such a useful video!
Glad you think so! 👍 John
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?
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
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!
Have you downloaded the spreadsheet with video? If not have a look at that because I think it solves that issue. John
Amazing 👍👍👍👍👍🙏
Thank you! Cheers 👍 John
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?
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 👍
@@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.
@@H4RI3 I think you're on to a good idea here so let me know if you get it working at some point 👍
Thanks! I like this as its a nicer/cleaner approach. but makes the rest of the cells useless/dead after the spill column.
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
@@Up4Excel thanks for the tip! didn't realize that was an option.
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
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
What is the solution for Filter and sor function as they are not available in Excel 2019.?
Unfortunately there isn't one, in this context anyway. Excel 2019 doesn't have dynamic arrays and that means it can't spill arrays of answers across cells as this solution requires.
You can kind of replicate a SORT function in Excel 2019 using RANK and then INDEX + MATCH. I have a video doing this for items in dropdowns if you're interested...it's here: ua-cam.com/video/j5n2faFrvWs/v-deo.html
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 ❤
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
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?
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 👍
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.
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
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.
I'm not sure what you mean. These drop-downs are in cell s so match the column width. Can you clarify?
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.
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
Thanks for your reply, I am on it and I will let you know soon as I find it how, thanks again.
Sir how can we create dropdown list in which we have to put 2 or more values
Sounds like you need a checkbox rather than a drop down list. Perhaps look that up?
Damn, this is perfect, but I can't make it work on Google Sheets. It gives error all around.
When stuff starts getting advanced Google sheets and Excel start to diverge, so not surprised.
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.
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.
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.
UNIQUE DOES NOT WORK FOUND THIS A WASTE OF TIME
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.
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.
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