Validation Question

[Guest]

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.

 

[Guest]

Click on D4, then drag over or
Highlight the area D4100

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

 

[Sandy Mann]

Validation for D4:

=LEN(D4)=7

Highlight D5100 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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk