Conditional Data Validation

[JBeth]

I am trying to have the drop down values of one cell dependent on the selected drop down value of another cell. What I have is:

Cell a1: Value can be either Yes or No
Cell a2: Value can be California, Colorado or Connecticut if a1 is Yes but if it is No then the only allowable values are California or Colorado. I have all 3 of these values in a4:a6. I'm trying to not have to have multiple lists with some of the same values.

I can't believe this isn't possible but I can't figure it out. Am I totally off-base?

 

[Sam_ExcelVault]

Hi JBeth - this is definitely possible to do, but you'll need to ensure the order of the 2nd drop down list options are listed in a specific order and will remain consistent, i.e. Yes will always have 3 dependent drop down selections and No will have two (unless you want to adjust the data validation formula).

First, ensure that the second drop-down options in A4:A6 are in this order: California, Colorado, Conneticut

Select the cell that you want your 2nd drop-down list to appear and try the following formula in the 'Source' box in the data-validation dialog box:

=IF($A$1="Yes",OFFSET($A$4,0,0,3,1),OFFSET($A$4,0,0,2,1))

This formula is defining the range of cells that contain values for the 2nd drop down list, stating at A4 as either 3 rows in length or 2 depending on the selection of the first drop-down (with the assumption that this drop-down is in cell A1).