Dependent Drop Down Lists

[BrandynBlaze]

I'm trying to build an interface that will allow others to select a specific product that will then give a subset of that specific lot. For instance my first list would be something like:

Product Type Inventory
Apple Red 2
Pickle Sweet 1
Orange Naval 5
Apple Green 3

I have my first list completed using data validation on a list that removes duplicates, what I need now is to be able to get a list of "Red" & "Green" in my second list, and then to return the value in inventory when one of those two is selected and I can't figure out how to do that either with built in functions or VBA. It seems like it should be easier than it is...

 

[Fluff]

You need dependant DV. Have a look here https://www.contextures.com/xlDataVal02.html

 

[BrandynBlaze]

Dang, I was hoping that wasn't the solution. I had used that method for another portion of my worksheet already and it worked very well but in that case the data was static. The list I'm working on now will be dynamic and added to a single running list. I think I can use vba to break out the data into separate lists but I was hoping there was an easier way to go about it since it seems like a fairly common task people would want to accomplish with Excel.

 

[Fluff]

The problem with populating DVs using VBA, is that the "list" wont exist when you next open the workbook. Which means you will get messages about xl repairing your workbook, or some such (I can't remember the actual warnings)

 

[MickG]

This example file might give you what you want !!!
https://app.box.com/s/dy449glwxp1ysm2lvtye2vjhwgm9m31m

 

[Eric W]

You can do all that without VBA. Consider:

	A	B	C	D	E	F	G	H	I	J
Product	Type	Inventory	Product	Type	Inventory	Type List
Apple	Red	Apple	Green	Red
Pickle	Sweet	Green
Orange	Naval
Apple	Green

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]{=INDEX($C$2:$C$100,MATCH(E2&"|"&F2,$A$2:$A$100&"|"&$B$2:$B$100,0))}[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I2[/TH]
[TD="align: left"]{=IFERROR(INDEX($B$2:$B$100,SMALL(IF($A$2:$A$100=$E$2,ROW($A$2:$A$100)-ROW($A$2)+1),ROWS($I$2:$I2))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

In this example, I assumed your table is in A:C, and your existing dropdown is in E2. Select an empty column to use as a helper column, I used I in this example. Put the I2 formula in, confirm with Control+Shift+Enter and copy down. Now select F2, and click Data Validation. Choose List as the type, and this as the Source:

=OFFSET(Sheet4!$I$2,0,0,SUM(IF(Sheet4!$I$2:$I$100<>"",1)))

Now put in the G2 formula with CSE. Now when you select a product in E2, the type list in I dynamically changes to list the correct types. The Data Validation in F2 looks at that list, and the formula in G2 gets the inventory amount.


Not real simple, but not terribly hard either. There are indications that Microsoft is making some changes to Excel that will make this much simpler though.