0

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.

  • I tried data validation -> formula =a1 <= B1 --- nor worked - blocking any change to cell (more or less than B1) – arul selvan Jun 28 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 at 12:11
0

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))

  • 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 at 10:13
  • like already mentioned, you cant use data validation on outputs from vlookup – user0 Jul 1 at 10:15
  • 1
    It's working as desired by the formula in data validation =or(S2128=0,Q2128 <= S2128) – arul selvan Jul 1 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 at 5:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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