stop duplicate entries using validation

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

Guest

I have a named range (one entire column) where I would like to enter SSNs using the data form, But I do not want any duplicate entries. I know the validation function can be used! But I can't seem to find the way. can anyone help?
 
Dennis,

Use Data Validation.

Select column A, goto menu Data>Validation, change the Allow option to
custom, and enter a formula of =COUNTIF(A:A,A1)=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dennis said:
I have a named range (one entire column) where I would like to enter SSNs
Click to expand...
using the data form, But I do not want any duplicate entries. I know the
validation function can be used! But I can't seem to find the way. can
anyone help?
 
Assume your entries are starting in A2, select the whole range (assume
A2:A200),
where A2 is the active cell, do data>validation, allow>custom and in the
formula box put

=COUNTIF($A$2:A2,A2)<=1

click OK

--

Regards,

Peo Sjoblom

Dennis said:
I have a named range (one entire column) where I would like to enter SSNs
Click to expand...
using the data form, But I do not want any duplicate entries. I know the
validation function can be used! But I can't seem to find the way. can
anyone help?
 
Bob, It worked thanks! I am sure you will hear more stupid questions from me in the future.

----- Bob Phillips wrote: -----

Dennis,

Use Data Validation.

Select column A, goto menu Data>Validation, change the Allow option to
custom, and enter a formula of =COUNTIF(A:A,A1)=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dennis said:
I have a named range (one entire column) where I would like to enter SSNs
Click to expand...
using the data form, But I do not want any duplicate entries. I know the
validation function can be used! But I can't seem to find the way. can
anyone help?
 
Dennis,

I look forward to it<G>

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob, I was a little premature, It does'nt work! In detail; I have a range D3:D65536 named lblAgSSNAI. I would like to open Data/Form, and enter information on a employee, I have a lot of people come and go, so in order to keep from entering the same person twice I thought I could stop an entire entry by makeing the SSN a non-duplicate entry. The formula you gave me stops me from duplicating the entry in D3, but not the rest (D4:D65536). Dennis

----- Bob Phillips wrote: -----

Dennis,

Use Data Validation.

Select column A, goto menu Data>Validation, change the Allow option to
custom, and enter a formula of =COUNTIF(A:A,A1)=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dennis said:
I have a named range (one entire column) where I would like to enter SSNs
Click to expand...
using the data form, But I do not want any duplicate entries. I know the
validation function can be used! But I can't seem to find the way. can
anyone help?
 
Dennis,

The reason is that Data Validation reacts differently if there are blank
cells in a named range. If there are going to be blank cells at
the end of the range, use a dynamic formula to name the range, and you'll
avoid the problem.

This is the formula you can use to define lblAgSSNA1 if D1 and D2 have data
in them

=OFFSET(Sheet3!$D$3,0,0,COUNTA(Sheet3!$D:$D),1)

or this if they are empty

=OFFSET(Sheet3!$D$3,0,0,COUNTA(Sheet3!$D:$D)-2,1)

The other option is to use this formula in the Data Validation itself, it is
probably easier

=COUNTIF(INDIRECT("lblAgSSNAI"),D3)=1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dennis said:
Bob, I was a little premature, It does'nt work! In detail; I have a range
Click to expand...
D3:D65536 named lblAgSSNAI. I would like to open Data/Form, and enter
information on a employee, I have a lot of people come and go, so in order
to keep from entering the same person twice I thought I could stop an entire
entry by makeing the SSN a non-duplicate entry. The formula you gave me
stops me from duplicating the entry in D3, but not the rest (D4:D65536).
Dennis
 
Bob, Thanks, but before you got back to me I tried =COUNTIF($D$3:$D$65536,D3)=1 and it works
(current cell
Dennis
 
Yes, that does work, but range names are better.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dennis said:
Bob, Thanks, but before you got back to me I tried
Click to expand...
=COUNTIF($D$3:$D$65536,D3)=1 and it works.
 
Back
Top