I have a spreadsheet with columns X, Z, AB, AD, AF and AH labelled with the
following column headings: Cheque Number 1, Cheque Number 2, Cheque Number 3
etc.

How do I set Data Validation on these columns to do the following:

1. Only a except a 9 digit Cheque Number.
2. To not allow any duplicate entires in any of the columns listed above.
For example; a value entered into Column Z will be cross referenced with
values in Z, AB, AD and AH to see if it has been duplicated.

Is this possible?

If not I would be quite happy with just duplicate values to be located in an
individual column only. For example, for the validation process to search on
a single column only.

Kind regards,

Simon.

Select the range of cells.
DataValidation
Select this 3 dropdown list
allow text length
data less than
maximun10
Done. Enjoy it.
"SiH23" wrote:

I have a spreadsheet with columns X, Z, AB, AD, AF and AH labelled with the
following column headings: Cheque Number 1, Cheque Number 2, Cheque Number 3
etc.

How do I set Data Validation on these columns to do the following:

1. Only a except a 9 digit Cheque Number.
2. To not allow any duplicate entires in any of the columns listed above.
For example; a value entered into Column Z will be cross referenced with
values in Z, AB, AD and AH to see if it has been duplicated.

Is this possible?

If not I would be quite happy with just duplicate values to be located in an
individual column only. For example, for the validation process to search on
a single column only.

Kind regards,

Simon.

1. Only a except a 9 digit Cheque Number.

Will any check numbers have leading zeros like this:

000123456

If so, what data type are these check numbers? Are they custom formatted
numbers so the leading zeros will display or are they entered as text?

--
Biff
Microsoft Excel MVP


"SiH23" wrote in message
...
I have a spreadsheet with columns X, Z, AB, AD, AF and AH labelled with the
following column headings: Cheque Number 1, Cheque Number 2, Cheque Number
3
etc.

How do I set Data Validation on these columns to do the following:

1. Only a except a 9 digit Cheque Number.
2. To not allow any duplicate entires in any of the columns listed above.
For example; a value entered into Column Z will be cross referenced with
values in Z, AB, AD and AH to see if it has been duplicated.

Is this possible?

If not I would be quite happy with just duplicate values to be located in
an
individual column only. For example, for the validation process to search
on
a single column only.

Kind regards,

Simon.

1- Name the range (MyRange)
2- Format range with text
3- Data/Validation (length=9)
4- Duplicates codes are refused in a name range (multi-areas).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect([MyRange], Target) Is Nothing And Target.Count = 1
Then
If Target < "" Then
If IsNumeric(Target) Then
If CountIfMZ([MyRange], Target) 1 Then
Application.EnableEvents = False
MsgBox "Duplicate!"
[A1] = Target
Target = Empty
Target.Select
Application.EnableEvents = True
End If
Else
Application.EnableEvents = False
MsgBox "Num please"
[A1] = Target
Target = Empty
Target.Select
Application.EnableEvents = True
End If
End If
End If
End Sub

Function CountIfMZ(champrech As Range, valCherchée)
Application.Volatile
temp = 0
For i = 1 To champrech.Areas.Count
For j = 1 To champrech.Areas(i).Count
If valCherchée = champrech.Areas(i)(j) Then
temp = temp + 1
End If
Next j
Next i
CountIfMZ = temp
End Function

http://cjoint.com/?imayZsh8JY

JB
http://boisgontierjacques.free.fr

On 11 août, 21:54, SiH23 wrote:
I have a spreadsheet with columns X, Z, AB, AD, AF and AH labelled with the
following column headings: Cheque Number 1, Cheque Number 2, Cheque Number 3
etc.

How do I set Data Validation on these columns to do the following:

1. Only a except a 9 digit Cheque Number.
2. To not allow any duplicate entires in any of the columns listed above.
For example; a value entered into Column Z will be cross referenced with
values in Z, AB, AD and AH to see if it has been duplicated.

Is this possible?

If not I would be quite happy with just duplicate values to be located in an
individual column only. For example, for the validation process to search on
a single column only.

Kind regards,

Simon.