Data Validation

[Guest]

Hi,

I am trying to add validation that willl do the following:

1. Prevent duplicate values being entered into a cell.
2. Only allow a 10 digit number being entered into a cell.

I know how to add these methods individually, but is it possible to set both
validation principles together on a cell or range of cells?

I would be most grateful for any help provided.

Many thanks,

John.

 

[JE McGimpsey]

one way:

Select your range (assume column A, with A1 active):

Choose Data/Validation:

Allow: Custom
Formula: =AND(ISNUMBER(A1), A1<10000000000, A1>=0, INT(A1)=A1,
COUNTIF(A:A,A1)<2)

 

[Peo Sjoblom]

Use AND(first,second)

=AND(COUNTIF(range,first_cell)=1,AND(ISNUMBER(first_cell),LEN(first_cell)=10))


something like this using real cells


=AND(COUNTIF($A$1:$A$10,A1)=1,AND(ISNUMBER(A1),LEN(A1)=10))


select A1:A10 with A1 as the active cell, then under allow use custom


note that it (all data>validation) can be bypassed by copying and pasting

 

[Guest]

Assuming the formula I gave you in your previous post works, you can modify
it like this:

=AND(ISNUMBER(A1),LEN(A1)=10,COUNTIF($A$1:$A$100,A1)<=1)

Again, change the cell references to meet your needs.

HTH,
Elkar

 

[Guest]

Many, many thanks for your wonderful help. That has worked perfectly. I would
very much like to learn to write complex formulas and wondered as to whether
you could advise a suitable learning resource?

 

[Guest]

Glad to help!

There are a lot of good resources out there, but practice is really the best
teacher. If you have the time, I'd suggest looking through these forums.
Find other people's questions, and try to answer them. Then, compare your
solutions to those of the experts. I think you'll find you'll pick up on a
lot of neat tricks that you might not come across in a formal training.
Plus, you may even help out someone else in need.

HTH,
Elkar

 

[Guest]

Many thanks, I shall do that.

Just one thing I wonder whether you could help me with? I am trying to apply
this formula to a a range of cells in column z with the column heading
'Cheque Number 1', but cannot get it to work.

This is what I am using:

=AND(ISNUMBER(X2),LEN(X2)=10,COUNTIF($X$2:$X$100,X2)<=1)

I am receiving an error message when I enter in a 10 digit number? Any ideas?

I apologise if I am being a nuisance ;-)

Many thanks,

Simon.