Cell should be disabled if condition, and if not data validation should be enabled

[Makri93]

Hello!

I am working on a sheet where the needs quickly is ramping up a bit. I am quite used to utilizing data validation rules, but now I have an added level of complexity. I will try to explain:

In the sheet there is a column within a table where I want the users to choose from a set list. Let's call this column Winter and people can choose vacation, work, etc. This comes from a list through data validation. Then I have a different column called "Full year" with only the possible values "Yes" or "No". The last column is called "Summer", and will contain the same data as in column "Winter". If people choose "Yes", then the cell in column "Summer" for that row is enabled and the same data validation as in the same row for "Winter" is active. If people choose "No", then the cell is grayed out and it becomes impossible to write anything in it.

This scenario could be described reverse too; if people choose "no" in column "Full year", the cell on that row in column "Summer" is disabled as a special rule. If people choose "Yes" the cell is untouched and normal data validation rules, same as in column "Winter" applies.

I have two theories as to how this can be done:

1. Create a separate list of values for column "Summer" that is similar to the first list for column "Winter" but added a value for N/A, with conditional formatting graying out the cell. However I do not know how to autofill the cell without using a formula.
2. Using VBA's, which I have no experience with.

 

[coulombevin]

Hi Makri93,

To avoid using VBA, I would use Personalised data validation with this formula:

- Name would be in column A;
- Full year would be in column B;
- summer and winter in C:D;
- List option would be in column $F$2:$F$1000;

Select C2:D* then go in DATA>Data validation and select "list" and in source place the following formula

=IF(LOWER($A2)="yes",$F$2:$F$1000,"")

Bests regards,

Vincent