Data Validation
 

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.

Data Validation
 

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)


In article ,
SiH23 wrote:

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.

Data Validation
 

Use AND(first,second)

=AND(COUNTIF(range,first_cell)=1,AND(ISNUMBER(firs t_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 datavalidation) can be bypassed by copying and pasting



--
Regards,

Peo Sjoblom



"SiH23" wrote in message
...
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.

Data Validation
 

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,A 1)<=1)

Again, change the cell references to meet your needs.

HTH,
Elkar


"SiH23" wrote:

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.

Data Validation
 

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?

"Elkar" wrote:

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,A 1)<=1)

Again, change the cell references to meet your needs.

HTH,
Elkar


"SiH23" wrote:

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.

Data Validation
 

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


"SiH23" wrote:

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?

"Elkar" wrote:

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,A 1)<=1)

Again, change the cell references to meet your needs.

HTH,
Elkar


"SiH23" wrote:

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.

Data Validation
 

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,X 2)<=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.



"Elkar" wrote:

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


"SiH23" wrote:

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?

"Elkar" wrote:

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,A 1)<=1)

Again, change the cell references to meet your needs.

HTH,
Elkar


"SiH23" wrote:

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.