Drop down questions

[ctjacobs2010]

In cell A1 I have a dropdown menu of the numbers 1-5.

-For B2 I am wanting if A1 equals 3,4,5 then B2 is blank
-If A1 equals 2 then I want B2 to be a dropdown option with the the word "Select" and "VAC"
-If A1 equals 1 then I want B2 to be a dropdown that says "Select", "VAC", "SICK", "BRVMT", and "JURY"

I have tried multiple ways but cant figure out how to make this work or if it is even possible.

Please Help,

Ctjacobs2010

 

[AndyPandy27]

Hi ctjacobs2010,

What you are trying to achieve is definitely possible, and known as Dependent Drop-Downs (i.e. the values in one drop-down depend on what value was chosen from another drop-down).

***Change the cell references used below to wherever you want to put your drop-down data***

In Cells C1:G1 enter the numbers 1-5.
Select C1:G1 then go to Formulas > Define Name; add the name "Options", then click OK.
In C2:C6 enter the values "Select", "VAC", "SICK", "BRVMT", and "JURY"
In D2:D3 enter the values "Select" and "VAC"
Select C2:C6 then go to Formulas > Define Name; add the name "List1"
Select D2:D3 then go to Formulas > Define Name; add the name "List2"

Select A1 then go to Data > Data Validation > Data Validation; select Allow: List; Source =Options; click OK
(You should now have a drop-down box with the values 1-5 in Cell A1).

Select B2 then go to Data > Data Validation > Data Validation; select Allow: List; Source =INDIRECT(CHOOSE(A1,"List1","List2")); click OK
(You should now have different options in your B2 drop-down, depending on what is selected in A1.

nb - if you have not selected anything in A1 when you create the Data Validation in B2, Excel may produce an error saying something along the lines of "...this evaluates to an error" - just click OK.

Hope this helps,

AP