Restrict a column to allow only unique entries?
 

I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique. Does
anybody have any experience?

Restrict a column to allow only unique entries?
 

In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique. Does
anybody have any experience?

Restrict a column to allow only unique entries?
 

Thanks Rob,
Does this go in the first cell of the column or somewhere
else?
-----Original Message-----
In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique.
Does
anybody have any experience?


.

Restrict a column to allow only unique entries?
 

Hi
highlight/select all cells for which you want to apply this validation.
Then enter
=COUNTIF($A$1:$A$1000,A1)=1
you have to change the following according to your needs:
1. The first paramenter of COUNTIF ($A$1:$A$1000) reflecting your
selected range
2. The second parameter (A1) to the fuppermost cell of your selection

The formula is entered in the data validation dialog (goto 'Data -
Validation' and choose formula as category)

Frank

wrote:
Thanks Rob,
Does this go in the first cell of the column or somewhere
else?
-----Original Message-----
In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique. Does
anybody have any experience?


.

Restrict a column to allow only unique entries?
 

....HOWEVER, the data validation has no effect when a worksheet value
is inserted/updated within MS Access via the link.

--

"Frank Kabel" wrote in message ...
Hi
highlight/select all cells for which you want to apply this validation.
Then enter
=COUNTIF($A$1:$A$1000,A1)=1
you have to change the following according to your needs:
1. The first paramenter of COUNTIF ($A$1:$A$1000) reflecting your
selected range
2. The second parameter (A1) to the fuppermost cell of your selection

The formula is entered in the data validation dialog (goto 'Data -
Validation' and choose formula as category)

Frank

wrote:
Thanks Rob,
Does this go in the first cell of the column or somewhere
else?
-----Original Message-----
In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique. Does
anybody have any experience?


.

Restrict a column to allow only unique entries?
 

Just to add (since there is often a misunderstanding of this), this will
only restrict someone manually editing a value in the cell. It will not
affect existing valuesin the cells, values produced by formulas or values
placed by code. Also, data validation is treated as a format, so someone
pasting into the cell will remove the validation.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi
highlight/select all cells for which you want to apply this validation.
Then enter
=COUNTIF($A$1:$A$1000,A1)=1
you have to change the following according to your needs:
1. The first paramenter of COUNTIF ($A$1:$A$1000) reflecting your
selected range
2. The second parameter (A1) to the fuppermost cell of your selection

The formula is entered in the data validation dialog (goto 'Data -
Validation' and choose formula as category)

Frank

wrote:
Thanks Rob,
Does this go in the first cell of the column or somewhere
else?
-----Original Message-----
In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would like to
restrict one column to require entries to be unique. Does
anybody have any experience?


.

Restrict a column to allow only unique entries?
 

Thanks Everybody,
I really need something that will work when I'm cutting
and pasting many values at a time to warn me if there is
duplication. Is there a way to do that? Gabe
-----Original Message-----
....HOWEVER, the data validation has no effect when a
worksheet value
is inserted/updated within MS Access via the link.

--

"Frank Kabel" wrote in message
...
Hi
highlight/select all cells for which you want to apply
this validation.
Then enter
=COUNTIF($A$1:$A$1000,A1)=1
you have to change the following according to your
needs:
1. The first paramenter of COUNTIF ($A$1:$A$1000)
reflecting your
selected range
2. The second parameter (A1) to the fuppermost cell of
your selection

The formula is entered in the data validation dialog
(goto 'Data -
Validation' and choose formula as category)

Frank

wrote:
Thanks Rob,
Does this go in the first cell of the column or
somewhere
else?
-----Original Message-----
In Cell A1: Data Validation. Custom.
=COUNTIF($A$1:$A$1000,A1)=1

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Gabe" wrote in message
...
I have an Excel table linked to Access. I would
like to
restrict one column to require entries to be
unique. Does
anybody have any experience?


.
.