Custom Validation

  • Thread starter Thread starter Ant
  • Start date Start date
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
 
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
 
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...
 
Back
Top