Validation Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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