Preventing duplicates in multiple tabs

[Spotniq]

Good afternoon,

I have a spreadsheet with three tabs and on each tab there are unique numbers in column A. Never should a number in column A be present on more than one tab. I'm in need of help coming up with a formula that will, when a new number is entered in column A of any one of the three tabs, immediately check to see that the number entered is not already present in column A of the other two tabs and if so I would like it to highlight the cell of the number (kind of like the highlight duplicates function). It would be great to also have a text shown stating this # is also located in Tab X but I think that's asking too much of a formula Any ideas would be greatly appreciated. thank you!

[bhenlee]

You can try this formula:

=IF(OR(COUNTIFS(Sheet1!A1:A10,Sheet2!A1)>0,COUNTIFS(Sheet3!A1:A10,Sheet2!A1)>0),"Duplicate","")

[newdoverman]

Create a rule for each worksheet in Conditional Formatting using the following formula:

=OR(AND(A1<>"",COUNTIF(Sheet2!A:A,A1)>0),AND(A1<>"",COUNTIF(Sheet3!A:A,A1)>0))

The above will work for Sheet1. For Sheet2 change the reference to Sheet2 to Sheet1. For Sheet3 change the references to be Sheet1 and Sheet2.

[Spotniq]

Create a rule for each worksheet in Conditional Formatting using the following formula:

=OR(AND(A1<>"",COUNTIF(Sheet2!A:A,A1)>0),AND(A1<>"",COUNTIF(Sheet3!A:A,A1)>0))

The above will work for Sheet1. For Sheet2 change the reference to Sheet2 to Sheet1. For Sheet3 change the references to be Sheet1 and Sheet2.

Thanks for the idea!! Im trying to get this to work on my end but for some reason keep getting "You may not reference operators such as unions, intersections, and ranges. I copied your exact formula on a new spreadsheet to bug it out and got tthe same error. Any ideas?

[popipipo]

I like this more:
=AND(A1<>"";OR(COUNTIF(Sheet2!A:A;A1)>0;COUNTIF(Sheet3!A:A;A1)>0))

[newdoverman]

Create each rule with the appropriate worksheet active. If you don't you will get that error. Each rule is created using the formula that references the other two worksheets.

In the workbook that I uploaded, you will see that there is only one rule per worksheet.