Cannot extend the range of data I'm trying to prevent duplicates i

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

wintergems - 19 Mar 2008 16:14 GMT

I used this website to help me set up the validation to stop duplicates,
which worked perfectly until I got to the cap of 2000.
http://www.ozgrid.com/Excel/prevent-duplicates.htm

Now I cannot change the range to go any higher than 2000 no matter what I
try. I have used the same directions I did before and it gives me errors and
does not work when i enter data past 2000. Help!!!

Reply to this Message

Tom Hutchins - 19 Mar 2008 18:09 GMT

I don't know where your cap of 2000 comes from. Is that the highest row
number you entered into your data validation formula? I just tested the same
validation down to row 10000 and it worked fine in Excel 2003. You could try
this:
- select the whole column where the validation is applied
- select Data >> Validation >> Clear All
- in the Allow dropdown box, select Custom
- enter this formula (changing the column references as needed):
=COUNTIF($A$1:A$10000,A1)=1
- click OK

Hope this helps,

Hutch

> I used this website to help me set up the validation to stop duplicates,
> which worked perfectly until I got to the cap of 2000.
[quoted text clipped - 3 lines]
> try. I have used the same directions I did before and it gives me errors and
> does not work when i enter data past 2000. Help!!!

Reply to this Message

Gord Dibben - 19 Mar 2008 20:26 GMT

First of all, I would not trust anything that came from a site where the author
uses the term "absolution" with respect to changing cell references from
relative to absolute.

What forgiveness has to with cell references is beyond me.

Second of all, why can't you edit the formula to

=COUNTIF($A$1:$A$12345,A1)=1

then use the format paintbrush to copy down or just select A1:A12345 before
entering the formula?

Gord Dibben MS Excel MVP

>I used this website to help me set up the validation to stop duplicates,
>which worked perfectly until I got to the cap of 2000.
[quoted text clipped - 3 lines]
>try. I have used the same directions I did before and it gives me errors and
>does not work when i enter data past 2000. Help!!!

Reply to this Message