2 specific secondary drop down lists can be chosen after chosen specific option in primary

I want to make a combination of drop down lists like below in Excel. Please help! Thanks

Scenario1 -
In Cell A1 (drop down of Main list): I choose "Item A".
In Cell B1: I can choose "Upper Part I" or "Upper Part II" which is Sub list#1 for Item A.
In Cell C1: I can choose "Lower part I" or "Lower part II" which is Sub list#2 for Item A.

Scenario2 -
In Cell A1 (drop down of Main list): I choose "Item B".
In Cell B1: I can choose "Upper Part K" or "Upper Part J" which is Sub list#1 for Item B.
In Cell C1: I can choose "Lower Part P" or "Lower Part Q" which is Sub list#2 for Item B.


Main list:
Item A
Item B

Sub list#1 for Item A:
Upper Part I
Upper Part II

Sub list#2 for Item A:
Lower Part I
Lower Part II

Sub list#1 for Item B:
Upper Part K
Upper Park J

Sub list#2 for item B:
Lower Part P
Lower Part Q

Hi,

Instructions on how to do what you are trying can be found at:

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

HTH
Steve

Originally Posted by SteveG

Hi,

Instructions on how to do what you are trying can be found at:

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

HTH
Steve

Hi Steve,

Thanks for reply, but the link you forwarded doesn't help my question.

Can you read my case again?

Hi,

The site I sent you has all the information needed to do what you want. You need 2 dependent drop downs. When I have a few minutes I'll send a sample workbook. The site, however, has a video turorial which is good if you can't wait.

Steve

Originally Posted by SteveG

Hi,

The site I sent you has all the information needed to do what you want. You need 2 dependent drop downs. When I have a few minutes I'll send a sample workbook. The site, however, has a video turorial which is good if you can't wait.

Steve

Thanks Steve,

But your link isn't fully resolve my problem. I know how to create data validation dependent lists that is only one to one relationship, or cascaded one-to-one-to-one relationship, however mine is a one to two relationship.

That means I have to choose one specific option in the primary drop down list, then the two secondary drop down lists will show the sub-information of that specific result in the primary drop down.

If you create named lists you can reference them using the INDIRECT function, which it seems like you already know.

What I would recommend for sub list 2 is to include an IF statement or VLOOKUP, depending on how many options you need to choose.

Example:

Create two lists, one called BOX2A, which includes LOWER PART I and LOWER PART 2 and one called BOX2B, which includes LOWER PART P and LOWER PART Q.

In the data validation for your second drop-down use formula that references those list names based on what is in the main menu.

=INDIRECT(IF(A1="Item A","BOX2A",IF(A1="Item B","BOX2B","")))

Or you can create a table of list names that should be associated with each specific Item in the main menu, which would be more efficient if you have several items in the main menu.

=INDIRECT(VLOOKUP(A1,G1:H5,2,FALSE))

G1:H5 being where your table is located, 2 being the column within the table where the names for the appropriate lists for the 2nd dependent box are found.

Here's a video that covers using the INDIRECT function for the first part of what you'll need to do http://www.youtube.com/watch?v=0Ey8-ILjjYs

Hi,

Here is a workbook that shows tonalqualityx's suggestions using the INDIRECT function.

HTH
Steve


2 Dependent Drop Downs.xlsx

Hi,

Attached is a file using the OFFSET and MATCH functions instead of a VLOOKUP. This will enable you to have a large list of Sub1 and Sub2 using the item (Item A, Item B etc...) in the match and a countif to determine the return for the drop downs.

HTH
Steve

Dependent Drop Downs2.xlsx

I like that. I never would have thought of using OFFSET & MATCH. I use VLOOKUP all the time, so it's usually my go to formula for stuff like this. Anyway, here's a link to a video that uses Ohlalayeah's scenarios with the VLOOKUP.
http://www.youtube.com/watch?v=0kYzmBncec4

Originally Posted by ohlalayeah

Yes, this looks good and is able to solve my problem. Can you make an excel file for me as a sample? ^^ Thanks!

Here it is... hope it's what you need :-).

Let me attach my file here.

I don't know why the second one doesn't work, Cells D20 and F20 on the front worksheet are the secondary drop down lists, but don't know why they are failed. Can you help fiixing it?

I'm having some trouble viewing it (my excel gets funky sometimes, I need a reinstall, but I dont have the disc). Anyway, it looks like your data validation isn't set to "List" in the section that isn't working? Could be my excel chopping it up though.

my primary list is on the worksheet "zList-General" under Column D; secondary lists are on worksheet "z69LSizeChart" columns E-R, the list for vlookup is under columns B-D.

Thanks!

Hmm.. I thought I just submitted a reply to this, but it didn't seem to post. Anyway, if you own a mac we could screen share through google chat. Might work if you don't own a mac, but I'm not sure. I see where everything is now, but I'm not following how it's supposed to fit together.

Also, it looks like in your VLOOKUP range your list names are incorrect. These should be exactly what you named the lists they correspond to. Also, the cell being referenced by the data validation (O16) is not in the lefthand column of your VLOOKUP range at all. This could be my excel chopping something up though...

Thanks! Do you mind fixing my excel file and attach here? Sorry, I am too stupid.

You're not stupid. It's a pretty involved sheet... I'm actually a bit uncertain still how all the pieces fit together.

I think I'm beginning to understand how it fits together... The names you've given your lists in z69LSizeChart could be shortened to match what is in 016. For example STL2101OT_otherstuff could lose the underscore and everything following it. Then your list names will match exactly the data in O16. Next, you can copy the formula from O16 into the data validation for D20 and surround it with an INDIRECT statement.

It should look something like this:
=INDIRECT(CONCATENATE(LEFT(D16,2),(MID(D16,4,7))))

Once your list names are updated to match you should be good to go, and can even eliminate O16.

I finally done! Thanks for your help so much so much!

Can you help me once more? I don't know why cell F20 (the first worksheet of my attached excel) doesn't work. I want the excel output a value referring to the Pantone code for the corresonding primary size on 69LSizeChart worksheet, after I have chosen D16 and D20 on the first worksheet.