Can i somehow have excel check for duplicate data when populating a cell?

[spaghettyhoop]

Hello. Been away for a couple of months on other projects, but im back to excel and feel like ive forgotten everything!

I have a big database of customers, each one has a unique reference number. They are spread accross a bunch of different tabs (21 in total to be exact)

One issue I have had is staff entering a customer whos already in the database, causing a duplicate entry. I dont need excel to tell us where the entry is, just to give some kind of indicator it already exists. I thought I may be able to use data validation/conditionality to turn the cell fill Red when it already exists in the data base.

The reason I think that method would be best, is that the sheets that data is entered on are seperate that the master sheet then pulls the data through from. Its the master sheet that would need to indicate a duplicate has been entered, as thats the only sheet where the entire database can be viewed.

Hope that makes sense, and if anyone can help me id be eternally grateful, as im thoroughly stuck!

 

[skorpionkz]

Can you upload the sample file?

 

[spaghettyhoop]

I cant right now due to my workplaces internet policy, I should be able to later once I have had chance to remove the information and create a blank example.

Although to simplify it, if we ignore the fact it links to other sheets, if I could just get it to automatically turn duplicate cells red, thats all it needs to do upon thinking about it. It sounds so simple in my head, but I cannot figure it out.

Essentially I want to be able to conditional format a cell to go "If already exists on a sheet, highlight red"

I have found a possible sollution using COUNTIF

http://office.microsoft.com/en-gb/e...using-conditional-formatting-HA001136616.aspx

So im going to give that a go and see if I can get it working