Preventing duplicates in a dynamic range

**garyi** · 01-17-2011, 10:53 AM

Hi all.

I have a column of cells some 400 deep with food items in.
This is a Vlookup and I want to prevent duplicate entries.

There is something along these lines in data validation:

=Countif(range,A1)<=1

However column A will need to be a dynamic range as the user can add to it.

So I need the data validation to check the dynamic range.

I have set a dynamic range with offset and its called checkdups.

I am sort of lost from here, In the first cell of the column I did data validation and typed:

=Countif(checkdups,A1)<=1

It did not return an error or anything, neither did it work.

In essence if a user types text into a cell in this column that already exists I want excel to tell them to try again.

Many thanks

I am also considering how to deal with people changing the text of these items, how does VLOOKUP deal with that. In essence this worksheet contains allt he ingredients and other sheets vlookup this one to obtain the price, what if a user decides they don't need tomatoes any more (for instance) and deletes it, what effect does this have on other sheets that have referenced tomatoes?

**NBVC** · 01-17-2011, 11:06 AM

If I select the whole column, eg. Column A and apply Data Validation with Custom rule:

=COUNTIF(A:A,A1)=1

Then I can enter a certain string only once in column A.

Is this what you mean?

**garyi** · 01-17-2011, 11:23 AM

I need excel to keep an eye on column A so that if a user puts in say 'Onions' and onions is already they it will give the user an error 'pick something else' or what ever.

Data validation would appear to cover this, but its applying it to a dynamic range that I am confused over. i.e. column A might have any number of cells in it.

**NBVC** · 01-17-2011, 11:28 AM

This is what the formula does..

After selecting column A and apply the data validation rule, then try typing Onions. It should accept it... now try typing Onions again in another cell within column A. It should not accept it.

Are we on the same page?

**royUK** · 01-17-2011, 11:28 AM

Apply it to the whole column

**garyi** · 01-17-2011, 12:10 PM

Hi yes, to the whole column I need to do, there are currently 400 cells in the column, but the user might add another 20 or 50, if I apply to the whole column won't the formula get its knickers in a twist over empty cells, hense whay I am trying to apply to a dynamic range using offset.

NBVC I tried your formula, it let me add a duplicate entry, without returning an error.