Multi Drop Down Lists & Duplicate Values

[forestavekids]

Hi guys, everyone here has been so helpful with my project, was hoping for a bit more help.

I am making a data file CSV for my clothing inventory. Trying to make a multi-drop down menu based on vlookup of the previous drop-down menu.

Getting a bit stuck, in down down menu 3, only the boys values should be showing but both genders are...

Even on drop down menu 2, both clothing & accessories, shoes, swimwear selections are showing.
multi-list attempt.xlsx
I am trying to have it only show values for genders, say clothing & accessories > boys > (then only show briefs, boxers, man-thongs).

[ChemistB]

Okay, might as well get the whole picture rather than go at this piecemeal. In your final spreadsheet, would there be a column between boys/girls (G) and full list (H) which would say Clothing & Accessories, Shoes, or Swimwear on each line?

[forestavekids]

Not sure I follow, columns E-H are for vlookup purposes for the drop-down menus.

[ChemistB]

Okay, seems you have an incorrect way of the way VLOOKUP works and its limitations.
For dropdowns 1 and 2, you have it referring to E and F, which is fine.

Then for dropdown 3, you simply reference column H.(H3:H8). What makes you think Excel will know to link this to what's in K13 and L13? Also, what is the logic that Excel will use to determine what in column H is swimwear, clothing, or shoes?

You are looking for a 3 deep dependent dropdown box. Do you know how to use defined names? I need to get an idea of where to start.

PS: There is no VLOOKUP on your worksheet.

[forestavekids]

Negative, I do not know how to use defined names..

If you could put it in an excel sheet I can usually figure it out though.

Lets say there are is an item category (clothing & accessories), 2 genders, 4 sub-categories of gender

Clothing & accessories/girls/bras
Clothing & accessories/girls/panties
Clothing & accessories/girls/thongs
Clothing & accessories/girls/g-string-divas
Clothing & accessories/Boys/briefs
Clothing & accessories/Boys/boxers
Clothing & accessories/Boys/man-thongs

So if I select boys, then only briefs, boxers, man-thongs should be the available selections in the next drop down.

[sandy666]

maybe it will help

Copy of multi-list attempt-1.xlsx


(sorry about rules but Data Validation lists has one function INDIRECT() and hard to explain here how to do it)

[forestavekids]

Mmm you lost me from #1, define all lists... lol...

[sandy666]

there is description how to do it
first you need define (vertical) list, like: (I said define? OK, create will be better?)
boys
girls
and next list, like:
clothing_B (clothing for boys)
shoes_B (shoes for boys)
etc...

your data should be organized correctly first so next steps are much much easier

[forestavekids]

Lol....

Still not following, can you fill that part in for me?

[sandy666]

which part?
I don't see any xlsx...

[forestavekids]

Basically what fields you are trying to build the list for #1 and under what value.

[sandy666]

hm, I don't know, you should know because you've the list (lists).
sorry about what I will write now
1. open NEW worksheet
2. take very basic list (mine is FList, Gender could be yours)
3.
A1: GENDER (this is header)
A2: girls (yellow)
A3: boys (green)

second list: (note colours)
4.
B1: girls (yellow - header)
B2: clothing_g (orange, cloth for girls)
B3: shoes_g (orange, shoes for girls)
B4: swimwears_g (orange, swimw. for girls)

break:
note: in A1 you've girls as position on list (yellow) but in next column B this is exactly the same name: girls - will be as header in B1 (yellow)
do the same with a boys (green) A3: boys (green) is a position on the list but in next next column C it will be a header in C1 (green)

positions from first list (vertical) (without header) are headers (horizontal) in the next columns.
means: yellow vertical is a yellow horizontal in the next column (in example you've two but there can be more)

three colours = three list

more?

[sandy666]

correction: new workbook, not worksheet, will be easier with named ranges

[bebo021999]

Try simple thing.
Use this formula in Data Validation of dropdown 3:
=IF($L$13="boys",$H$3:$H$5,$H$6:$H$8)

[sandy666]

bebo,
what if boys will change to boyz? it will not be changed automatically
and next what about more than, say: 5 lists?
now I can manage lists and names from one range

[bebo021999]

bebo,
what if boys will change to boyz? it will not be changed automatically
and next what about more than, say: 5 lists?
now I can manage lists and names from one range

I follow the original post #1, with boys/girls in dropdown2.

[sandy666]

Ok, no problem

[forestavekids]

Yep so lets give this another try lol..

Made a new spreadsheet that might explain it a little better. The values of drop down #3 must be defined by the choice of category in drop down 1, then gender in drop down 2

[forestavekids]

May be best to break it down on the spread sheet I attached.

[bebo021999]

Here you go............

[forestavekids]

Yep so that is what I am looking for, though I am not sure how you got only the girl values to show in drop down #3. I was hoping show formulas would have revealed this!

I did replicate the same outcome for the row below the one you had filled in using =vlookup(d15,g:h,2,false) which was the same formula I first started with that would not work. So I am not sure what I had been doing wrong lol.

[bebo021999]

Try to hit F3 to see where the validation come from.

[forestavekids]

Ah a bit stuck again. Trying to apply this concept to a new list, but I get stuck on the gender specific values showing up in drop down #3.

What command are you using to specify that only that items tied to the gender of drop down #2 are showing up?

[bebo021999]

Stay in cell E3 (dropdown 3), Ctrl-F3, Name List, Edit, Copy formula out and paste it in any availabla cell, you can see:

=OFFSET(Sheet1!$H$1,MIN(IF(IF(Sheet1!$F$2:$F$11=Sheet1!$C14,IF(Sheet1!$G$2:$G$11=Sheet1!$D14,Sheet1!$H$2:$H$11,""),"")="","",ROW(Sheet1!$H$2:$H$11)-MIN(ROW(Sheet1!$H$2:$H$11))+1)),,MAX(IF(IF(Sheet1!$F$2:$F$11=Sheet1!$C14,IF(Sheet1!$G$2:$G$11=Sheet1!$D14,Sheet1!$H$2:$H$11,""),"")="","",ROW(Sheet1!$H$2:$H$11)-MIN(ROW(Sheet1!$H$2:$H$11))+1))-MIN(IF(IF(Sheet1!$F$2:$F$11=Sheet1!$C14,IF(Sheet1!$G$2:$G$11=Sheet1!$D14,Sheet1!$H$2:$H$11,""),"")="","",ROW(Sheet1!$H$2:$H$11)-MIN(ROW(Sheet1!$H$2:$H$11))+1))+1)

Adjust the range of F,G,H to adapt your actual range
Note: F and G column are always sorted.

[forestavekids]

Yea.... Thinking there is an easier way than to go through that formula and start replacing values.

[bebo021999]

Use Find and Replace (Ctrl-H) to replace $11 with,i.e, $20

[forestavekids]

Okay for this example yet. But on the fly what is the proper way to define the values of the previous selection