Excel Data Validation for only unique values breaks after a few entries

Question

I am trying to prevent duplicate values and am using the =COUNTIF(range,value)=1 formula within the Data Validation wizard. My range has been set at $AD$1:$AD$200 and I put my value at AD1.

I know that the value is supposed to change to match the cell I am currently entering a value into - but this happens in a weird way. If I'm typing in AD48 and then I check back in the data validation wizard, the value in the countif formula has changed to AD48.

But, sometimes the value will change to AD1-million-and-something. I haven't been able to discover a consistent reason for this, although I have a feeling that it's happening for cells that are the last filled cell in my column. Still, I've tried un/checking Ignore Blank Cells and that hasn't affected anything.

The frustrating thing too is that sometimes I'll enter a duplicate value and data validation will correctly put up the error message but then I'll enter a duplicate value again and it will allow it.

And sometimes I'll enter a value I know is definitely unique, and it will still put up the error message.

I'd like to eventually extend this to a COUNTIFS formula if I can (I'm checking for duplicates within 3 different unique ID columns) but Data Validation can't seem to handle the single formula.

Advice? I am using Excel 2013.

Answer 1

Hi,

This question is outside the scope of this site and to be sure you get the best answer, it should be asked on MSDN.

Regards.

Answer 2

Thanks for the reply. I'm not a developer so I'm not sure which forum would be of best help to me.