Validation on a calculated cell

[FM1]

I am trying to input data validation on a calculated field. The calculated field (sum of 10 fields above it) must not total over 100%. I have used the following in the data validation menu:

Allow: whole number

Data: less than or equal to 1

This doesnt seem to be working when I input values of a total greater to 100 from its input cells however, it does return an error if I manually input a number over 100%, then it gives me an error message.

Edit - sample sheet attached.

[teylyn]

Hi,

you need to put the data validation on the input fields, not the result. Data validation will never evaluate a calculated cell, it only validates manual data entry, so that's where you want your validation to be.

For both input fields, i.e. E2 and E6, enter this custom data validation formula

=$E$2+$E$6<=1

hth

[ncmay]

Use Data Validation in cells E2 and E6 with the formula:

Please Login or Register  to view this content.

[FM1]

That has worked a charm.

Thanks very much for your help.

[teylyn]

please don't quote whole posts (it's just clutter), especially not two of them, and especially if all you want to say is "thanks". We're bright enough to get that without the quote.

Appreciate the thanks.