How do I flag if an entry is duplicate across multiple sheets?

[2davidc8]

Let's say I have 4 sheets in a workbook, and I want to check only Column A for duplicates. I may have more than 4 sheets, but I want to flag a duplicate in only those 4 sheets.
If I'm making entries in Sheet2, for example, and the same Column A entry already exists in any of the 4 sheets in question, including Sheet2, I want to highlight it or flag it in some way.

I know how to use Conditional Formatting to find duplicates in a single worksheet. I now want to find if a duplicate occurs in more than one sheet.

Can I do this without resorting to VBA?

Thanks!

 

[Fluff]

You could use something like

=OR(COUNTIFS(Sheet1!A:A,A2),COUNTIFS(Sheet2!A:A,A2))

where A2 is the first cell in the applies to range.

 

[2davidc8]

@Fluff
Thank you for your reply. I'm afraid I don't quite understand your solution.
1) Where would I place this formula?
2) My understanding is the A2 in your example should represent a "criteria". How is A2 a criteria?
What I'm looking for is, for example:
if any value or entry in Column A of Sheet1 also appears anywhere in Column A of Sheet1, Sheet2, Sheet3 or Sheet4, both duplicate occurrences should be flagged (by changing the color of the text or the fill color), so for example,
if Sheet1!A5 has "Tom", and Sheet4!A32 also has "Tom", both "Tom's" should turn red.
Can you guide me as to how I could do this?

 

[Fluff]

You can use that formula in conditional formatting and when you type in a values that exists in another sheet it will be highlighted.

 

[2davidc8]

@Fluff
Thank you for your solution.
It worked!
(However, note that the use of the OR function is not necessary.)

 

[Fluff]

You're welcome & thanks for the feedback.

 

[2davidc8]

@Fluff
Oh, two subtleties:

I should say, the OR function is not absolutely necessary. It can be used if you want to string all the sheets into one formula, as in your example.

And, in the reference to the current sheet, the formula should say > 1, as in:
COUNTIFS(A:A,A2)>1
Otherwise, every entry in the sheet will be highlighted.

 

[Fluff]

It can be used if you want to string all the sheets into one formula, as in your example.

Firstly that is what you asked for & secondly, to do that the OR is needed.

 

[2davidc8]

Yes, sorry, I misunderstood.