excel data validation in cells wit

Question

In excel I was wondering how you go about adding a simple list (data validation) to a range of cells but have the data validation list only appear if the adjacent cell is filled? For example a simple 'Yes, No' list in column B but have it only display if column A is populated, if blank then no data validation. is this possible? Thanks

Answer 1

My interpretation of your requirement is not quite the same as @teylyn’s (though hers makes more sense to me!) Say your ‘Yes, No’ list is in F1:F2 and you require to validate from Row1 onwards:

Select B1
Data > Data Tools – Data Validation, Data Validation, for Allow: choose List, and under Source: enter:

=IF(ISBLANK(A1),,F$1:F$2)

Click OK and copy B1 down as far as required.

Answer 2

You can use the principles of dependent data validation. You need to set up two ranges with the data validation options. Call one range "Yes" and one range "No" and let them have the same values.

Enter yes or no into cell A1 and define the data validation for cell B1 as a list with the reference

=indirect(A1)

If A1 is empty, the data validation list will be empty and any value can be entered in the cell. If A1 contains either yes or no, the data validation will show the list of values.