Data validation advice please

[ipbr21054]

Hi,

I need a basic check for duplicate entries.
Each day i enter a customers name into cell A4 then when i leave the cell i wish the code to check to see if there is a duplicate in my current range of which is A4:A149

I have used data validation as so,

Select all cells in question A4:A150
Data validation
Allow = Custom
Formula =COUNTIF($A$4:$A$149,A4)=1

My problem is i get a false alert for when a new name is entered and does not exist in my range.

Please can you see where i went wrong.

On a side not as i enter names into A4 each day and the list becomes longer will the formula in data validation auto update itself ?


If you think i am better off using VBA etc then no problem please advise.

Many Thanks

 

[DHayes]

Hi,
I dont see a problem with the formula as I have used this with great success.
change it so that when you enter new data you don't have change the validation

[COLOR=#333333]=COUNTIF($A:$A,A4)=1[/COLOR]

 

[ipbr21054]

Just an update before i do anything.
Ive started agin in respect of selecting all the cells in column A and then data validation.

I see a message box that reads,
The selection contains some cells without data validation settings,do i wish to extend data validation on these cells.

First of all how can i find out what this is before i continue with my code for duplicated names etc.


Thanks

 

[ipbr21054]

Second update.
I have found a few cells in column A which dont have the data validation applied.

How can i tell what data validation is then applied to the cells in column A ?

 

[ipbr21054]

Hi,
Ive got it working to a fashion but now stuck & asking if you can advise how i can continue.

Currently my cells that contain customers names is A6:A149
When i next need to enter data i click a macro button which now adds a new row at A6,i then type in cell A6

This is where my problem is.
If i have a customer in my range called VERN & i type VERN in cell A6 i can continue no problem.

If however i say edit a customer in any cell in my range then type VERN i now get my message box pop up saying Customer Exists.

Why is the data validation not picking up the name VERN when i type it in cell A6 ?

The is the code i am using in the data validation box =COUNTIF(A:A,A3)>1, when i click in each cell i see the A3 increase A4 A5 A6 etc etc.
But when i click in cell A6 and check data validation there is no code ???

Please could you assist.

Thansk

 

[ipbr21054]

I have now used conditional formatting and works a treat.
Thanks