Macro: choosing a drop down list from another drop down list list!

**123wc** · 12-01-2011, 03:35 PM

Hello,

I would like to choose a list based of the value selected in another list. For example:

In A1 I have a drop down list that allows the user to choose between 1 or 2. If the user selects 1 then Cell A2 will have a certain drop down list, e.g.: A, B, C. If the user selects 2 then Cell A2 will have a different drop down list, e.g.: X, Y, Z.

How do I do this with VBA?

Thanks a lot in advance and I am looking forward to your replies

**tigeravatar** · 12-01-2011, 04:05 PM

123wc,

Not sure why'd you need VBA. An IF() statement in the Data Validation Source will accomplish this, you would just need to set aside an area in the sheet to hold the lists.

Attached is an example workbook based on the criteria provided. If A1 = G1 (1) then A2's list will be = G2:G4 (which is A, B, C). If A1 = H1 (2) then A2's list will be = H2:H4 (which ix X, Y, Z). Else, A2's list will be = I1 (which is a blank cell).

Here's A2's Data Validation Source formula:
=IF($A$1=$G$1,$G$2:$G$4,IF($A$1=$H$1,$H$2:$H$4,$I$1))