Data Validation

S

SiH23

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.
 
J

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)
 
P

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
 
E

Elkar

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
 
S

SiH23

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?
 
E

Elkar

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
 
Top