Data validation with formulas issues.

Hello all,

I am trying to make a form which computes the cells of two columns into one (e.g. A1 * B1 in cell C1) and then have the sum of all the sum in the computed column to be less than a threshold. The data validation works when I enter the number in the computed column as numbers, but when the sum of the computed column only has formulas (like the one in parentheses above, then it does not work.

Example:

A B C
1 50 50
2 40 80
4 10 40


I want the sum in column C to be less than 150, so in my custom data validation I enter = sum($C$1:$C$3)<=150. It does not give me a warning if C1 is =A1*B1, C2=A2*B2 and C3=A3*B3. It does work if the sum data are entered manually though.

Am I missing anything?

Thanks for your help!
Cheers

Hydro.

Sure. Here is an example. If you enter manually a number that is over 200, then you get a warning. If you use the formula calculating the sub sums, then it does not work as in the example. Let me know if that helps clarifying my issue. Thanks!!!

Wonderful. Thank you!!!

Hello, my issue is solved but there is another issue. Apparently, data validation does not work when one clicks outside the cell to validate the entry instead of using the enter key. Do you all know if this is a bug or if there is a way to prevent this from happening?
Thanks.