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?

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?

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?


.

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?


.

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


.

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?


.

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?


.
.