Data validation advice please

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
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
Code:
[COLOR=#333333]=COUNTIF($A:$A,A4)=1[/COLOR]
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,813
Office Version
  1. 2007
Platform
  1. Windows
I have now used conditional formatting and works a treat.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,970
Messages
5,599,106
Members
414,287
Latest member
Rbwester

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top