In Google Sheets, the Data menu > Data validation is the only built-in way to reject the entry of certain values in a cell or range of cells. In this article, I’m going to share with you how to reject a list of items in data validation in Google Sheets.
How to Reject a List of Items Using Data Validation
There are two examples I’m going to explain in this article:
- Data validation to reject/restrict a list of items (item codes, item numbers, names, or any value) in a range.
- Rejecting a list of items if it is already available/present in another sheet or another range.
Let’s dive into each example and learn more about them.
Block (Reject Input of) a List of Values
Let’s say I want to reject the input of the values “pending”, “withheld”, or “amended” in column A. Here is the formula (data validation rule) to do this:
Formula #1: =not(REGEXMATCH(to_text($A1),"pending|withheld|amended"))
The REGEXMATCH formula matches the strings “pending”, “withheld”, and “amended” and returns FALSE if there is no match. The outer NOT function makes the FALSE TRUE.
When the formula returns TRUE, such entries are allowed in the corresponding cell in column A. Otherwise, they get blocked/rejected.
You can use any string, number, or a list of strings as shown above. When there is more than one value to match, separate them by the Pipe symbol (|). For an exact match, use the expression with the ^ and $ symbols.
Block (Reject Input of) a List of Values if Available in Another Sheet
Let’s approach the same scenario from a different angle. Instead of excluding certain values in column A, this time I want to block or reject the input of the said values in column B if they are available in another range. Here’s the formula to achieve this:
Formula #2: =REGEXMATCH(to_text($B1),"pending|withheld|amended") * ArrayFormula(SUM(REGEXMATCH(to_text(Sheet2!$A$1:E$10),$B1)*1))=0
The first part of the formula uses REGEXMATCH to match the strings “pending”, “withheld”, and “amended” in cell B1 in “Sheet1”. It returns TRUE if there is a match, otherwise it returns FALSE.
The second part of the formula uses ArrayFormula and SUM to match the string in cell B1 in “Sheet1” with the range A1:E10 in “Sheet2”. It returns TRUE if there is a match, otherwise it returns FALSE.
If the cell B1 value is available in “Sheet2”, the output of the second part of the formula would be a number greater than 0.
To summarize, the validation rule to reject a list of items if available in another sheet in data validation can be written as:
=part_1_formula * part_2_formula = 0
This means the entry is only allowed if the output is 0.
That’s all about how to reject a list of items in data validation in Google Sheets. For more useful tutorials and tips, visit Crawlan.com. Enjoy!
