Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

If you are counting uniques then they will all be less than 2 think you need 1
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

Data Validation==Custom== =COUNTIF(D:D,D1)<2 worked OK for me.


"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

Sorry didn't get a good look at that, all you are doing with your formula is
counting how many times the value in D1 appears in column D, and then asking
if it is less than 2. What are you doing next? What are you trying to get in
the end?
--
-John Northwest11
Please rate when your question is answered to help us and others know what
is helpful.


"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

I tried again with a new worksheet and it worked fine but still doesn't work
on the first one. I cleared all and I did it again. I don't know what is
wrong?

"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

Maybe your numbers are text?

Format all to General.

Copy a blank cell.

Select your data range and EditPaste Special(in place)AddOKEsc.

Does the CF work now?


Gord Dibben MS Excel MVP

On Thu, 15 Mar 2007 11:39:21 -0700, Mary wrote:

I tried again with a new worksheet and it worked fine but still doesn't work
on the first one. I cleared all and I did it again. I don't know what is
wrong?

"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

No, it does not work and I don't know what is wrong. I set CF and it allows
me to type the same value 3 times and the fourth time I got the restricted
message.

"Gord Dibben" wrote:

Maybe your numbers are text?

Format all to General.

Copy a blank cell.

Select your data range and EditPaste Special(in place)AddOKEsc.

Does the CF work now?


Gord Dibben MS Excel MVP

On Thu, 15 Mar 2007 11:39:21 -0700, Mary wrote:

I tried again with a new worksheet and it worked fine but still doesn't work
on the first one. I cleared all and I did it again. I don't know what is
wrong?

"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2

Sorry

I used the term CF not DV which is what you are trying to use.

Did you see the couple other replies you got?

Toppers stated that your formula worked for him.

Works for me.

I select all of column D and DataValidationAllowCustom

=COUNTIF(D:D,D1)<2 and OK out.

Type qwerty in D1

Type qwerty in D2 and get the "restricted" message.


Gord



On Fri, 16 Mar 2007 11:58:00 -0700, Mary wrote:

No, it does not work and I don't know what is wrong. I set CF and it allows
me to type the same value 3 times and the fourth time I got the restricted
message.

"Gord Dibben" wrote:

Maybe your numbers are text?

Format all to General.

Copy a blank cell.

Select your data range and EditPaste Special(in place)AddOKEsc.

Does the CF work now?


Gord Dibben MS Excel MVP

On Thu, 15 Mar 2007 11:39:21 -0700, Mary wrote:

I tried again with a new worksheet and it worked fine but still doesn't work
on the first one. I cleared all and I did it again. I don't know what is
wrong?

"Mary" wrote:

Hi,
I am trying to set up column D as unique but everytime that I type the below
formula doesn't work. If I enter the same value twice it takes.
=COUNTIF(D:D,D1)<2