Prevent duplicate entries

[Julee]

Dear all,

I need to validate a particular cell, let's say A20, in the sheet1 that it would check all column B in the sheet2 whether there already exists such a text. If such a text already exists in column B of the sheet2, I need that it would inform the user with the MsgBox that such a text already exist. Is it possible to do this with excel VBA?


Thanks a lot!

 

[dcofer]

Julee,

Sorry, I failed to note in your original post that the list is on Sheet2 and not Sheet 1.

I just tested this and it worked for me. You can use the same Data Validation steps I listed above, but use this formula instead: =COUNTIF(Sheet2!B:B,A20)<1

As I said, I tested it and an error message popped up if I entered data into cell A20 on Sheet1 that was an exact match of data in Column B on Sheet2. It allowed entry of non-matching data.

Just curious, are your sheets named "Sheet1" and "Sheet2"? If not that could be the problem.

Hope this works for you.

 

[alansidman]

Perhaps if you uploaded your spreadsheet to a shared location, Box.net, dropbox, etc. we might be able to assist more. dcofer makes a good point. Sheet1 and Sheet2 are the names used. You will need to change them in my code if it is something different. I tested my code and it works. So the next steps are up to you.

Alan

 

[kvsrinivasamurthy]

What is the problem faced.

 

[Julee]

Everything works! Thanks a lot!