Highlight Duplicate values across multiple sheets

[jrazzabo630]

I have a spreadsheet with an exhaustive list of names (ALL NAMES).

I then have several other sheets (Silver, Gold, Top, Art, Music, Latin, Key) that have the names of various award recipients (a subset of the ALL NAMES).

I want to be able to apply conditional formatting (or something) highlighting the names on the exhaustive list that appear on each additional sheet (that way I can remove any names that are not receiving awards) 

[#2]

@jrazzabo630 

In the following, I will assume that the names are in A2 and down on each sheet.

Select A2:A1000 or however far down the data go on the ALL NAMES sheet.

A2 should be the active cell in the selection.

On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula

=OR(ISNUMBER(XMATCH(A2, Gold!A:A)), ISNUMBER(XMATCH(A2, Silver!A:A)), ISNUMBER(XMATCH(A2, Top!A:A)))

Add more ranges as needed. If a sheet name contains spaces or punctuation, enclose it in single quotes: 'Sheet Name'!A:A

Click Format...
Activate the Fill tab.
Select a highlight color.
Click OK, then click OK again.

[#3]

Thank you for your response - it doesn't appear to be doing anything

[#4]

@jrazzabo630 

It works for me in a small test workbook. Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#5]

@HansVogelaar The formula worked just fine for me! You just have to remember to close with 3 parentheses at the end of it and select a format to highlight or otherwise distinguish your data.

[#6]

@HansVogelaar When I try it gives me an error message of "You may not use references to other workbooks for Conditional Formatting criteria"

[#7]

@DanZ24 

Conditional Formatting can refer to ranges on other worksheets within the same workbook, but not to ranges on worksheets in other workbooks.