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

  • Thread starter Thread starter wintergems
  • Start date Start date
W

wintergems

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!!!
 
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
 
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
 
Back
Top