Explanation
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the input is rejected with a popup message.
In this case, we have previously defined the named range “xlist” as D5:D11. This range holds characters that are not allowed.
The formula we are using for data validation is:
=COUNT(FIND(xlist,B5))=0
Working from the inside out FIND function is configured with xlist for “find text”, and cell B5 as the text to search. Because we are giving FIND an array with multiple values, FIND returns an array of results, one for each character in the named range “xlist”. For cell B5, the result from FIND looks like this:
{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
Each #VALUE error represents one character not found. If we try to enter, say, “demolition@”, which includes a restricted character, FIND returns:
{#VALUE!;11;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}
Note the second value in the array is now 11.
Next, the COUNT function returns the count of all numbers in the array. When the array contains no numbers (i.e. no restricted characters) COUNT returns zero, the expression returns TRUE, and data validation succeeds. However, When the array contains no numbers (i.e. there is at least one restricted character found) COUNT returns a number, the expression returns FALSE, and data validation fails.
The characters that appear in the named range xlist can be customized fit requirements.
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
Explanation
Data validation rules are triggered when a user adds or changes a cell value.
The AND function takes multiple arguments (logicals) and returns TRUE only when all arguments return TRUE. The DATE function creates a proper Excel date with given year, month, and day values.
Because we want to allow only dates in the month of June 2016, we give AND with two logicals.
The first logical tests that input to C5 is greater than or equal to June 1, 2016:
C5>=DATE(2016,6,1)
The second logical tests that input to C5 is less than or equal to June 30, 2016:
C5<=DATE(2016,6,30)
If both conditions are TRUE, the AND function returns TRUE and input passes validation. If either condition is FALSE, AND returns FALSE and input fails data validation.
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
