Explanation
Data validation rules are triggered when a user adds or changes a cell value.
This custom validation formula simply checks the year of any date against a hard-coded year value using the YEAR function.
When a user enters a value, the YEAR function extracts and compares the year to 2016:
=YEAR(C5)=2016
When the years match, the expression returns TRUE and validation succeeds. If the years don’t match, or if the YEAR function is not able to extract a year, validation fails.
Date this year
To allow only dates in the current year, you can adjust the formula like this:
=YEAR(C5)=YEAR(TODAY())
The TODAY function will return the current date on an on-going basis, so formula returns TRUE only when a date is in the current year.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case C5.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
Explanation
Data validation rules are triggered when a user adds or changes a cell value.
In this case, we need a formula that returns FALSE as long as entries in C6:C9 sum to a total equal to or below 1000. We use the SUM function to sum a fixed range and then simply compare the result to 1000 using less than or equal to. Note the range C6:C9 is entered as an absolute reference to prevent the reference from changing automatically for each cell that data validation is applied to.
Each time a number is entered, the validation is triggered. As long as the sum remains less than 1000, validation succeeds. If any entry causes the sum C6:C9 to exceed 1000, validation fails.
Note: Cell references in data validation formulas are relative to the upper left cell in the range selected when the validation rule is defined, in this case B5.
Data Validation Guide | Data Validation Formulas | Dependent Dropdown Lists
