Attached is the easiest sheet you can imagine. It’s just a column of six numbers with the seventh cell being Sigma SUM()
LOValidation.ods (8.8 KB)
I applied Validation to B8 simply to prevent overtyping
The validation is the same formula as the Sigma function =SUM(B2:B7)
Why does it let me type a random value into B8?
Note: It prevents me from changing the formula - try focusing B8 and then attempt to change it to Sigma Anything()
The sole purpose of the validation is to prevent overtyping.
I know it works like a charm with the formula shown in the graphic which is taken from a working sheet - the working sheet where I was endeavouring to “protect” some totals by enforcing Data>Validation with the cell formula of
=ROUND(I12+SUM(I40:I41);2)
For the record; I have read the help available via the URL Link [Validity Help]
Interestingly, the offline help produces the text below for F1 against “Custom”
Custom
Only allow values that result in the formula entered in the Formula box to return TRUE, when the formula is calculated with the entered value. The formula can be any expression that evaluates to a boolean value of TRUE or FALSE, or returns a numerical value, where a nonzero value is interpreted as TRUE and 0 is interpreted as FALSE.
Formulas can use relative referencing. For example, if cells A1:A4 were selected, the cursor is in cell A1 and ISODD(A1) is entered into the Formula box, then only odd numbers could be entered into cells A1 through A4.