1

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.

Improve this question
2
  • I tried data validation -> formula =a1 <= B1 --- nor worked - blocking any change to cell (more or less than B1)
    – arul selvan
    Jun 28, 2019 at 11:39
  • The validation formula =a1<=b1 is working in a blank sheet. But, I have some vlookup formula in in a1 and b1. When I want to change the value in A1 by overwriting the vlookup function, I want the entered value must not exceed B1 (which is result of another vlookup function)
    – arul selvan
    Jun 28, 2019 at 12:11

2 Answers 2

Reset to default
2

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

Improve this answer
-1

If values in A1 & B1 cells output values as a result of formulae this won't be possible to "restrict" via Data Validation. In such cases, the common practice is to wrap your formulae in IF statement and direct it towards outputting nothing or error if the condition is not met. A1 then be like:

=IF(B1=0, your_A1_vlookup_formula_here, 
 IF(your_A1_vlookup_formula_here>B1, , your_A1_vlookup_formula_here))

Improve this answer
4
  • The formula is getting the value all right. But, what I want is data validation. The user must not be allowed to increase the value of A1 (by typing in a value) which more than B1 (the Bi is hidden from the user). A1 and B1 are both having a vlookup formula to start with. The user can type in any value only if B1 is evaluated to zero
    – arul selvan
    Jul 1, 2019 at 10:13
  • like already mentioned, you cant use data validation on outputs from vlookup
    – user0
    Jul 1, 2019 at 10:15
  • 1
    It's working as desired by the formula in data validation =or(S2128=0,Q2128 <= S2128)
    – arul selvan
    Jul 1, 2019 at 10:27
  • The above formula is not always working. I modified the validation formula slightly (for the range Q2:Q =or((S2:S)=0,((Q2:Q)-(S2:S))<=0 ) ........ It is blocking values higher than contained in S, but allow any value if S=0 (in same row)
    – arul selvan
    Jul 2, 2019 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.