• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Trouble with multiple dependent drop down lists showing correct options.

L

Lucko4Life

New Member
Hello,

I am trying to make 4 drop down lists where you can select in order the Brand, Type, Product, and Size.

The major issue I am having is that all products of the same type are showing up for all brands that have that same type of product.
For example, Cat Only Toothbrush is showing up under Dog's brand, and Dog Only Toothbrush is showing up under Cat's brand, because the products are both the same Type (Toiletries).

I understand why it is showing up this way, I just do not know how to set up everything so that it shows up with properly matching products.

Is it possible to set up four dependent drop down lists based off of a single table that's arranged in the specific way found in my attachment and link? I found a Google Sheets file that someone made where it has exactly what I am hoping to accomplish.
It has one table with all the data, and then has 4+ drop down lists that are each dependent on the lists before them so the products are properly matching. They did write a script that is on the file, and I am honestly not sure if it is only because of the script that they can set them up that way, or if the script is for something else.
I would really prefer to have it this way, as I would only have to use my existing product info table and not take up any more space with additional tables.

If that is not possible in Excel, is it possible to make the 3rd drop down list (Product), dependent on both the 2nd and 1st drop down lists? Or is there another solution to my issue?

Here is the link for the Google Sheets file that contains an example of what I want (must make copy):
Google Sheets Example

And my Excel workbook that contains my current issues described above is also attached.

If anyone can help me out or point me in the right direction, I would tremendously appreciate it! Thank you so much.
 

Attachments

  • Dependent Dropdown Lists - 3 Practice (Share).xlsx
    41.9 KB · Views: 13
Well I don't know if anyone even saw my post, but I did find a solution to part of what I was looking for, and I wanted to add that here in case anyone ever comes across this and has the same issue.

I found this link, at about 3/4 down the page:

https://www.contextures.com/xlDataVal02.html

It explains how to make a dependent drop down list dependent on at least two other drop down list values.

You have to combine the two names of the lists that come before it and make a separate list + named range. For example, say you have three drop down lists, Brand > Type > Product, and the Product is dependent on both the Brand and Type. For the Product list, you would write the brand name + type together, make the list under it, then define it as named range with the the same name (for example, "TargetFurniture"). For the data validation for the drop down list cell, you would type

=INDIRECT(SUBSTITUTE(C8&D8," ",""))

C8&D8 being the cell location of the other two drop down lists.

Although I am unaware how to remove the blanks spaces when I have it in combination with a table. So if anyone sees this, I would appreciate help with that.
 
Try,

1] "Step 3" (the 3rd dropdown list) table list changed from G1:H6 to M1:N9

2] Create "the 3rd dropdown list" by:
In N14 >> Data Validation >>
>> Allow : List
>> Source : =OFFSET($M$1,MATCH($B14&"/"&$E14,$M$2:$M$9,0),1,COUNTIF($M$2:$M$9,$B14&"/"&$E14),1)
>> OK

then, copied to N16

3] Follow the similar steps and create "the 4th dropdown list" by your own.

Regards
Bosco
 

Attachments

  • Dependent Dropdown Lists - 3 Practice (BY-1).xlsx
    27.1 KB · Views: 14
Lucko4Life said:
If anyone can help me out or point me in the right direction,
Click to expand...
Probably not what you're looking for, but maybe you may have a direction.
Take note, in the example, there is a VBA code that automatically cleans the Validation Cell when you update the previous cell.
The complete instructions are in the example.
 

Attachments

  • Lucko4Life-navic42020.xlsb
    56.8 KB · Views: 7
vletm said:
Lucko4Life
As You could see there has been more than 7 views with You attached file in less than 24hrs.
( ... again, have You read: https://chandoo.org/forum/threads/site-rules-new-users-please-read.294/ There are some text for You too. )

Have You tried to search from Chandoo.org?
eg this: https://chandoo.org/wp/robust-dynamic-cascading-dropdowns-without-vba/

With VBA, there would be different possible solution.
Click to expand...

Oops, I definitely was't complaining, if that's what you thought I meant by "I don't know if anyone even saw my post".
I know there were a few views, but I wasn't sure if anyone actually read through it all since my post was so long, that's more of what I meant.
I definitely did not mean for that to come off as me being impatient or anything. I just wanted to share what I had found, as I was very excited to find a partial solution :)

Thank you for the link, I actually had come across that as well, and my newest table is set up very similar to that. Also similar with what Navic posted.
My real issue was with the third drop down needing to be dependent on the two drop downs before it, instead of just the one before it. Bosco posted a different solution to the one I found, which I really appreciate.

As for VBA, I wish I new more about it, I have only been using Excel for a month now. I was really hoping to find something similar to the script in the Google Sheets Example I posted. All the drop down lists are all dependent on the other lists' values before them, and only one data table is needed, and that table is set up in the particular way I am wanting. I believe it is the script that allows it to be set up that way. I will keep looking to see if this is possible :)

Thank you for your response :)
 
navic said:
Probably not what you're looking for, but maybe you may have a direction.
Take note, in the example, there is a VBA code that automatically cleans the Validation Cell when you update the previous cell.
The complete instructions are in the example.
Click to expand...

My newest table is actually set up similar to this. Thank you very much for taking the time out of your day to look into my questions :)
 
navic said:
Probably not what you're looking for, but maybe you may have a direction.
Take note, in the example, there is a VBA code that automatically cleans the Validation Cell when you update the previous cell.
The complete instructions are in the example.
Click to expand...
Please refer to your file in Post #.05

Your step 3&4 dropdown list no. 3&4 results are wrong,

For example :
If B3="Lovies", C2="Usable" M (J)", your dropdown list in D3 show "Holly" and "Fir".
It should result none. Please check with the source table G13:K31

Your mistake as same as the OP, dropdown list 3 &4 is a 2 criteria Lookup
Please check my reply in Post #.04 for comparison.

Regards
Bosco
 
Last edited:
Hi @Bosco
Thanks for the reaction and warning.

[EDIT]
btw:
English is not my mother tongue.
I understand the OP that he wants to have "Holly" on the both lists. That's why I did this. (please see range H26:I29 in ny post #5)
If OP was wrong then I was wrong. ;)
Nevertheless thank you for the warning, of course and because of other visitors to this thread.
Best regards
 
Last edited:
navic said:
Hi @Bosco
Thanks for the reaction and warning.

[EDIT]
btw:
English is not my mother tongue.
I understand the OP that he wants to have "Holly" on the both lists. That's why I did this. (please see range H26:I29 in ny post #5)
If OP was wrong then I was wrong. ;)
Nevertheless thank you for the warning, of course and because of other visitors to this thread.
Best regards
Click to expand...

The create of dependent dropdown list is based on the following "Source Table"
61185

Here's your Level 1 to Level 3 lists, the Level 3 lists setup is wrong (consider Type only)
61186


Here's my Level 1 to Level 3 lists, the level 3 lists setup in concatenate Brand/Type
61187

The 3rd dependent data validation dropdown lists that depend on the selections in the first two dropdowns.
That is to select a Brand and Type, then select a Product in the selected Brand and Type.

The formula used in the 3rd dependent data validation dropdown lists should be a 2 criteria LOOKUP

Regards
Bosco
 
navic said:
Hi @bosco_yip
Thanks for the clarification.
Note! I hope that visitors to this thread will ignore my post #5
Best regards
Click to expand...
Hi,

You can rectify your file Level Lists/formulas and then forward us the file with rectification.

However, please remove all the links in your file "Note!" sheet which is not necessary.

Regards
Bosco
 
Hi @bosco_yip
The two of us did not understand well.
I have taken into account the possibility that the OP has a list of the same products that are on two different lists.
Please do not be angry with me, I do not have time to devote myself to this thread further.
It is important that the OP solved his problem.
btw: I apologize for my bad English.
With respect
 

Attachments

  • Lucko4Life-navic42020-2.xlsb
    56.5 KB · Views: 6
You must log in or register to reply here.
Back
Top