+Neil Firth any chance of a dynamic drop down list to work if list 2 needs to have a duplicate? Example: Data entry Hlcm MA - Mechanical assembly - Obstructed MA Tensor MA - Mechanical assembly - Untorqued component Where the dashes separate List1, List2, and List3 I would like the drop down list to differentiate between Hlcm MA's mechanical assembly and Tensor MA's mechanical assembly so that each would show their respective List3 items. So far I can't get it to work. Only by converging my mechanical assembly lists (converging all List3 items for "Mechanical assembly" results) am I able to get List3 to show the data I need, however this negates the purpose of using a drop down list because once I choose "Mechanical assembly" for List2 (of any List1), List3 will show all items tied to "Mechanical assembly". Mind you this is just an example of the much larger data set I'm trying to organize. The only other option I have found is to name my mechanical assemblies different. (i.e. "Mechanical assembly1" for Hlcm, and "Mechanical assembly2" for Tensor). Again, the issue with this, is in the amount of data I'm trying to organize. Mechanical assembly is List2 for at least 7 different List1 throughout my spreadsheet, each with their own different List3 results and just for uniform purposes I'd like for all of them to show the same title "Mechanical assembly" but have different List3 results based on my List1 selection. In other words, I'm trying to build a drop down list where List2 can have the same name (if possible) for multiple List1 items and still give me different List3 items (respective to List1). I.e. List1 (commas used to separate items) = APS, Hlcm MA, Tensor MA List2 = Mechanical assembly List3 (commas used to separate items): APS = DC motor, Motion control Hlcm MA = Obstructed MA, Snap ring Tensor RTV MA = Bent bellows shaft, Untorqued component
I have done everything right but formulae seem not to work for the second cell. The indirect in the second cell does not depend on the choice of the first cell??
Awesome stuff , and just what I thought I was looking for. I am trying to modify this to fit a project but I have hit a wall. I am trying to have the second dropdown pull information from a separate sheet within the workbook however from what I can tell the set up you have here only allows for same sheet output only, for the second dropdown. (I can retrieve all the information I need for the first dropdown.). Any ideas?
Hy there, loved your video, i have an intresting question i have a table (in one row): monday, tusday, wendsday, thursday, friday for week 40 (columns down) then a drop down: project 1, project 2, ... project 10 then on monday week 40 i select project 2 on tusday week 40 i want to have all the other projects except project 2 (9 in total) by the time we reach friday week 40 we have only 6 project left in the list (the projects that where not selected from the list in the days before) then we go to the next row: week 41 and select all the other 5 project left. but when we reach week 42 all the project where used and so we want to have all the 10 projects once again. Any ideas ? Thanks
First, great video. Now I downloaded the accompanying workbook and modified your formula to my needs but I am not getting it to work. Do you have a spreadsheet with the individual formulas in it. So I can understand the different formulas
How to do this with names and addresses. Like Sony Pty Ltd, and address 201 Smith St Chicago 20102. the names are in one column and the addresses in second column. Want to make it that can type in cell and it selects the name which I did use your other video, but changed it as found another formula that worked with if select company name then it displays address in address field.
Neil thanks for a very educative video. I have gone through the video and have successfully learned your trick. However, I have an issue which I believe is minor to you. With this formula the second drop down worked perfectly: =INDIRECT(C7&"2:"&C7&C8) But the drop down does not work when I replace C7 and C8 with the formulas in the cells, so that I can delete the helper columns (=INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&"2:"&SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&COUNTIF(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&”:”&SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)),”?*”))). Please help with that.
As this question is 3 years old, the original poster probably has long forgotten it. For anyone else, I think the problem he had is that he didn't include the sheet name where the reference cells were located in the named range. Instead of $B$3, you would need 'example 3'!$B$3. Personally, I would try to avoid the "substitute" and "address" functions to merely shorten the overall formula and make it a bit easier to read/debug. But as a starting point, it is excellent. This was a very well done video.
hi I just watch your video .. wich is brillant.. I would like to set up a family budget and trying to comnbine 2 templates.. I tried to do it my self but I have to admit that my brain is not so logical as urs guys .. I would like to add to my budget an expense calendar . the purpose of it is to add the data in the calendar and find the balance in another sheet .. . I accepte any help to make it real as I think it can be more convenient for people .I can explain more in detail by mail if you are interested to help me.
![Create multiple dependent drop-down lists in Excel [EASY]](http://i.ytimg.com/vi/daCvyt9E8s4/mqdefault.jpg)
![Create multiple dependent drop-down lists in Excel [EASY]](/img/tr.png)
Good Lord - way to make me feel like a complete Luddite when it comes to using Excel! Impressive chops. :)
You are a life saver ! Thank you so very much.
+Neil Firth any chance of a dynamic drop down list to work if list 2 needs to have a duplicate?
Example:
Data entry
Hlcm MA - Mechanical assembly - Obstructed MA
Tensor MA - Mechanical assembly - Untorqued component
Where the dashes separate List1, List2, and List3
I would like the drop down list to differentiate between Hlcm MA's mechanical assembly and Tensor MA's mechanical assembly so that each would show their respective List3 items.
So far I can't get it to work. Only by converging my mechanical assembly lists (converging all List3 items for "Mechanical assembly" results) am I able to get List3 to show the data I need, however this negates the purpose of using a drop down list because once I choose "Mechanical assembly" for List2 (of any List1), List3 will show all items tied to "Mechanical assembly". Mind you this is just an example of the much larger data set I'm trying to organize.
The only other option I have found is to name my mechanical assemblies different. (i.e. "Mechanical assembly1" for Hlcm, and "Mechanical assembly2" for Tensor). Again, the issue with this, is in the amount of data I'm trying to organize. Mechanical assembly is List2 for at least 7 different List1 throughout my spreadsheet, each with their own different List3 results and just for uniform purposes I'd like for all of them to show the same title "Mechanical assembly" but have different List3 results based on my List1 selection.
In other words, I'm trying to build a drop down list where List2 can have the same name (if possible) for multiple List1 items and still give me different List3 items (respective to List1).
I.e.
List1 (commas used to separate items) = APS, Hlcm MA, Tensor MA
List2 = Mechanical assembly
List3 (commas used to separate items):
APS = DC motor, Motion control
Hlcm MA = Obstructed MA, Snap ring
Tensor RTV MA = Bent bellows shaft, Untorqued component
This is very helpful, thank you! But after watching this video I'd like to know how I can learn how to put different formulas together like this?
This is brilliant - just what I wanted to do.
I have done everything right but formulae seem not to work for the second cell. The indirect in the second cell does not depend on the choice of the first cell??
Thanks man 💓
How do you get it to work if your named ranges are two words instead of one?
Hi Neil,
How to auto-update blue when you change yellow? Right now when you change category the blue doesn't automatically change. Thanks.
Can this work for multiple rows of selections? For example, if i wanted to choose Hats->Cap and the row below it Fruit->Melon.
Awesome stuff , and just what I thought I was looking for. I am trying to modify this to fit a project but I have hit a wall. I am trying to have the second dropdown pull information from a separate sheet within the workbook however from what I can tell the set up you have here only allows for same sheet output only, for the second dropdown. (I can retrieve all the information I need for the first dropdown.). Any ideas?
awesome .. Thanx for share
Hy there, loved your video, i have an intresting question
i have a table (in one row): monday, tusday, wendsday, thursday, friday for week 40 (columns down)
then a drop down: project 1, project 2, ... project 10
then on monday week 40 i select project 2
on tusday week 40 i want to have all the other projects except project 2 (9 in total)
by the time we reach friday week 40 we have only 6 project left in the list (the projects that where not selected from the list in the days before)
then we go to the next row: week 41 and select all the other 5 project left.
but when we reach week 42 all the project where used and so we want to have all the 10 projects once again.
Any ideas ? Thanks
First, great video. Now I downloaded the accompanying workbook and modified your formula to my needs but I am not getting it to work. Do you have a spreadsheet with the individual formulas in it. So I can understand the different formulas
How to do this with names and addresses. Like Sony Pty Ltd, and address 201 Smith St Chicago 20102. the names are in one column and the addresses in second column. Want to make it that can type in cell and it selects the name which I did use your other video, but changed it as found another formula that worked with if select company name then it displays address in address field.
Great, thanks.
Neil thanks for a very educative video. I have gone through the video and have successfully learned your trick. However, I have an issue which I believe is minor to you. With this formula the second drop down worked perfectly: =INDIRECT(C7&"2:"&C7&C8)
But the drop down does not work when I replace C7 and C8 with the formulas in the cells, so that I can delete the helper columns (=INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&"2:"&SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&COUNTIF(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)&”:”&SUBSTITUTE(ADDRESS(1,MATCH($B$3,$A$1:$Z$1,0),4),1,”“)),”?*”))). Please help with that.
As this question is 3 years old, the original poster probably has long forgotten it. For anyone else, I think the problem he had is that he didn't include the sheet name where the reference cells were located in the named range. Instead of $B$3, you would need 'example 3'!$B$3. Personally, I would try to avoid the "substitute" and "address" functions to merely shorten the overall formula and make it a bit easier to read/debug. But as a starting point, it is excellent.
This was a very well done video.
hey mate do you know much about If statements?
I would like this correct if you do
=IF(F47=11=30=40,F47-15)))) it comes up with faild
When u are using nested if's then don't use ",0" to define False Condition. Instead start ur next "if" formula directly.
Excellent :)
amazing... thanks!
Great
hi I just watch your video .. wich is brillant.. I would like to set up a family budget and trying to comnbine 2 templates.. I tried to do it my self but I have to admit that my brain is not so logical as urs guys .. I would like to add to my budget an expense calendar . the purpose of it is to add the data in the calendar and find the balance in another sheet .. . I accepte any help to make it real as I think it can be more convenient for people .I can explain more in detail by mail if you are interested to help me.