Custom Validation

A

Ant

I am looking for an example of using a custom validation rule.

If I have some numbers in cells A1:A8 and set up a custom validation rule in
cell A9 that says in the formula box =SUM(A1:A8) then I can enter that
formula in that sell but also could enter =SUM(A1:A5) or even the number 25
or the text “Test”. Am I missing the point?

Can anyone give me an example of when this would be used and how it works?



Thanks
 
B

Biff

Hi!

The basic premise for data validation is to only allow
entires in a cell or range of cells that meet specific
conditions.

In your example you did not test the conditon of cell A9.
If you would have entered this:

=A9=SUM(A1:A8)

Then, assume the sum of A1:A8 is 35.

Now, cell A9 will not accept any value but 35.

Another example:

Suppose you only want either a "Y" or "N" (for YES or NO)
in cell A9.

The formula to restrict the cell entry to only those 2
values would be:

=OR(A9="Y",A9="N")

There are some limitations!

You may want to restrict the entry in A9 to either Y or N
but that does not force an entry. In other words, someone
could just leave the cell blank!

Also, you can copy/paste any value to cell A9 even if it's
restricted to either Y or N.

It's not totally bullet proof but it is very useful.

Biff
 
A

Ant

Thanks Biff,
Thats just what I was looking for.

Biff said:
Hi!

The basic premise for data validation is to only allow
entires in a cell or range of cells that meet specific
conditions.

In your example you did not test the conditon of cell A9.
If you would have entered this:

=A9=SUM(A1:A8)

Then, assume the sum of A1:A8 is 35.

Now, cell A9 will not accept any value but 35.

Another example:

Suppose you only want either a "Y" or "N" (for YES or NO)
in cell A9.

The formula to restrict the cell entry to only those 2
values would be:

=OR(A9="Y",A9="N")

There are some limitations!

You may want to restrict the entry in A9 to either Y or N
but that does not force an entry. In other words, someone
could just leave the cell blank!

Also, you can copy/paste any value to cell A9 even if it's
restricted to either Y or N.

It's not totally bullet proof but it is very useful.

Biff
Click to expand...
 
Top