Google-sheets – Dynamic Data Validation in Google Sheets

data validationformulasgoogle sheetsgoogle-sheets-arrayformula

I want to restrict a value in a cell based on another cell in the same row.

The value in A must not exceed what is in B. If the value in B is zero, any figure is allowed in A

A | B
---
4 | 9     – allowed

5 | 0     – allowed

6 | 5     – the value in A should not be allowed

How to set up data validation for this in column A.

I want all rows in Column A to have this validation rule.

Best Answer

Set the data validation for A1 as follow:

On Cell range set the cell range from Column A.
On the Criteria dropdown select: Custom formula is
On the formula input box add this formula: =OR(B1=0,A1<=B1)
On the On invalid data radio buttons selet Reject input

Dont' forget to click Save

Related Solutions

Google Sheets Regex – Using Regex in Data Validation

The regular expression should be ^[a-zA-Z0-9,./()]*$, where $ at the end means the end of the string. This forces the entire contents to consist of the specified characters.

Also, you don't normally pass a range into data validation formulas: it should be the reference to the upper left corner of the range. For example, if your range is A2:E20, the formula in data validation should be

=REGEXMATCH(TO_TEXT(A2), "^[a-zA-Z0-9,./()]*$")

This formula will then be interpreted for other cells according to the way relative references are treated. This is the same behavior as with conditional formatting.

Google Sheets – Use Date Data Validation to Compare Columns

Short answer

Yes, Google Sheets can.

Instructions

Optional. Select the range to apply the data validation
Right click on the selected range or click on Data > Validation...
Optional. Write/edit the range to apply the data validation
Click on Criteria and select Date
Set the rule options including a formula using the top left cell reference, i.e. =A1

Click on the Save button.