Is there a way to use data validation on a cell with a formula?
For example, if on cell: A1
I use: Data Validation, Criteria: Allow: Custom, Formula: A1>0
A formula like this will error as an “Invalid value”:
=1/2
But a numerical entry will work:
0.5
How can this be fixed?
In a formula you need to specify the cell to be evaluated as well, e.g. A1=1/2 would work, otherwise 1/2 is just a constant that is always true (because not 0).
The leading = is not needed (and discarded).
Update:
That was not immediately clear to me. But thinking twice I get it…
If you apply a fraction format to the cell like # ??/?? then an input of 13/32 will be accepted (without leading equal sign thus not a formula). To be able to input 1/2 either have AutoCorrection disabled or press Ctrl+Z once immediately after input if it was auto-corrected to ½
If the date separator is not / then even pre-formatting the cell as fraction isn’t necessary, otherwise input of 4/5 would yield a date.
Clarification: I’m having trouble when I use a formula in the cell, not with the formula used in the validation.
The custom formula must return a sequence of allowed contents, not a condition.
Sorry. I mixed things up.
(The following isn’t wrong, but somehow off topic.)
Formulas arent GreaterThan or LessThan. Their results (if numbers) may be.
If you want a cell with a validation to accept specific formulas, you need to treat these formulas as strings.
Very strange idea. But actually the cell getting a formula this way will try to parse and tokenize it. In case of success the result will be shown. Of course there can’t be an adaption in reference if the formula string is containing a “relative address part”.
If you want to automatically “validate” entered formulas regarding the result, you need to use ConditionalFormatting. Show red background e.g. if a formula result doesn’t meet the conditions.
Regard the comment below by @erAck.
I’m not sure I understand your intention, but that doesn’t sound correct. The custom formula must be a logical expression that yields true or false. For example this on A1 works
OR(A1=1;A1=2;A1=3)
to allow an input of only 1, 2 or 3.
Or AND(0<=A1;A1<=10) to allow any numeric input between 0 and 10, including.
A custom formula returning a sequence of values does not work. However, it does work if entered for a Cell range source, like {1;2;3} is perfectly valid there.
And relative references are adapted when copy-pasting a cell with validity.
That is sad to hear. I wanted to prevent a user from entering an invalid value rather than simply change the appearance of the value. The intent of using a spreadsheet for this application was to let the computer do the calculations.
Even as simple example, if I wanted to make sure a user entered a value between 0 and 0.4 inches. I can’t expect them to enter 0.40625 inches instead of =13/32
Please do not use the Answer field if not an answer to the original question, Comment or edit your question instead to provide further details. Thanks.
I can’t expect them to enter 0.40625 inches instead of =13/32
But you may enter 0 13/32, and it will be accepted and converted to number correctly (just always provide whole part, even if 0).
For simple fractions, like 1/2, you may see that it gets auto-corrected to ½. You might need to disable auto-correction of those in Options.
mikekaganski,
Thanks for pointing that out, it’s a step in the right direction for the more simple cases. What I was hoping for was something more along the lines of: =(2*B1 + 5+29/64)