In-cell dropdown dependent on cell value (Data Validation)

[RandomStu]

I often get asked questions like this: There are two in-cell dropdowns
created with Data Validation. The user wants the options in one of the
dropdowns to be determined by the value selected in the other. Below
is how I explain the solution. Since this is just something I worked
out myself, I'm wondering if there's a simpler method, or a better way
to explain it. Thanks.

************

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 D15, 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.

************

Stuart Resnick
http://www.allexperts.com/ep/1059-73728/Excel/Stuart-Resnick.htm
http://stuart-randomthoughts.blogspot.com/

 

[muddan madhu]

In cell D23
put 2 & 4

in Cell E2:E5
put 3,6,9

select D23, name ( define the range ) it as XX
similarly E2:E5, name ( Define the range ) it as YY

in cell A1 put validation as xx,yy ( allow: list )
in cell B1 put validation as =indirect(A1) ( allow : list )