Drop down menu depending on another drop down

**gjergji** · 12-03-2012, 05:43 PM

So on column F there is a drop down menu which you can pick from but i want column h to be dependent on the information displayed on column F. So if I pick HYFOL 14 on column F. Then I will have the option of picking these
110359P
165679-U
155011-U
131653-U
PLA0050133
You can add tabs and columns. Thank You!

**BobBing** · 12-03-2012, 06:09 PM

See the attached item.

On the orders tab I've highlighted in yellow what I have added. (Column A Row 11 and Column B row 11)

On the products page I also added in the indexing that you need to do to have the lists to be dependent. (Columns K:L)

Also look at the name manager to see what was added in there.

the uconnection and uproducts are what you use for the data validation reference.
Now practice will allow you to understand it better!

Hope this helps.

**JBeaucaire** · 12-03-2012, 08:06 PM

1) highlight column A on the Formulas tab, then type PRODUCTS into the Name box, this gives a name to your sorted list in column A.

http://screencast.com/t/MGpm2e5AX2gC

That's the only named range you need. You can expand the data on this tab as much as you want, will keep working.

2) On the Orders tab, click on H2 and open the Data Validation settings, and use this:

Allow: List
Source: =OFFSET(Products, MATCH($F2, Products, 0)-1, 1, COUNTIF(Products, $F2), )

Don't worry if it gives an error, which it will if F2 is empty.

3) Copy F2 downward.

You're done. The cells in column H will have a list based on the choice made in column F of the same row.

**JBeaucaire** · 12-03-2012, 08:12 PM

Oh, if you want to be able to keep using the column H to list more components without having to keep selecting values in column F, use this DV formula instead, it will create a list based off of the last value in column F up through that cell. To get a new list, you need only make a new column F selection.

Source: =OFFSET(Products, MATCH(INDEX($F$1:$F2, MATCH("zzzz", $F$1:$F2, 1)), Products, 0)-1, 1, COUNTIF(Products, INDEX($F$1:$F2, MATCH("zzzz", $F$1:$F2, 1))), )