Hi,

I would be most grateful if someone could offer some help or advice.

I wish to Validate a range of cells from D4 to D100 which will only allow
the following:

1. only a 7 figure number to be entered.
2. to not allow a duplicate 7 figure value to be entered.

Many thanks,

Simon.

Click on D4, then drag over or
Highlight the area D4:D100

at the menu, Data, Validation, on Settings Tab select Custom.
In the formula box paste in

=AND(LEN(D4)=7,COUNTIF($D$4:$D$100,D4)<=1)

and OK (out)

Write back if problems..


"SiH23" wrote:

Hi,

I would be most grateful if someone could offer some help or advice.

I wish to Validate a range of cells from D4 to D100 which will only allow
the following:

1. only a 7 figure number to be entered.
2. to not allow a duplicate 7 figure value to be entered.

Many thanks,

Simon.

Validation for D4:

=LEN(D4)=7

Highlight D5:D100 and then use the Validation:

=AND(LEN(D5)=7,COUNTIF($A:$I,D5)=1)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"SiH23" wrote in message
...
Hi,

I would be most grateful if someone could offer some help or advice.

I wish to Validate a range of cells from D4 to D100 which will only allow
the following:

1. only a 7 figure number to be entered.
2. to not allow a duplicate 7 figure value to be entered.

Many thanks,

Simon.