Expert: Stuart Resnick - 10/19/2009

Question
Hi, I am trying to design an order form where my client can select a product code from a drop down list in column A.  I am using VLookups to return the name & description of the product in column B & column C.  However, in column D, I want to give my client a drop down list of quantities he can order but the quantities need to be specific to the product.  I was wondering if it was possible to do this - have a drop down list change depending on the selection in column A.  I have tried a basic VLookup but it returns a value of 0

Thanks


Answer
Example: say you have 2 product codes: XX and YY. For XX, the quantity options are 2 or 4. For YY, the quantity options are 3 or 6 or 9.

Start with a blank Excel sheet. In A1:A2, enter

XX
YY

In C1:C5, enter

XX
XX
YY
YY
YY

In D1:D5, enter

2
4
3
6
9

Select cell F1, and use Data Validation to make it an incell dropdown in which the user can select XX or YY (your Data Validation List Source will be =$A$1:$A$2).

Now select G1 and do another Data Validation. This time, your List Source will be:

=offset($D$1,match($F$1,$C$1:$C$5,0)-1,0,countif($C$1:$C$5,$F$1),1)

Select XX in cell F1, and you'll see that the cell G1 dropdown offers the choices 2 or 4. Select YY in cell F1, and the G1 dropdown offers the choices 3 or 6 or 9.