Conditional formatting based on cells matching a column of another sheet

Question

I've got a spreadsheet made on Google Sheets that contains 11 sheets, and each sheet is a set of things that I'm considering buying. Some sets contain the same individual pieces as something else. For example item A might be on sheet A and sheet D.

I'd like to make some kind of formula to highlight all duplicates, so that when I was looking through the sheet I could see whether buying item A will help me complete more than just the set that I'm looking at. So I can look at a set and if it's mostly green I know there's more value in buying it as almost all of the contents will also go towards another set.

I know how to do it so that they glow if they match on the same sheet.

$A2=$B2    

However I'm not sure how to do it across sheets, or how I'd include if statements. As the colour would have to change if it matches any cells in column A on any sheet. They're also not in the same order on different sheets so while item A might be in A3 on sheet A, it might be in A17 on sheet D.

I'm not sure how possible something like this is, but I'd appreciate any help.

Answer 1

Google Sheets does not allow direct references to cells in other sheets in conditional formatting formulas. But this can be circumvented with indirect:

= A2 = indirect("Sheet2!A2")

formats the current cell (A2) if it's the same as the contend of A2 on Sheet2.

But you want to check whether the content is duplicated anywhere in column A of another sheet. This can be done with match: select the column A2:A of the present sheet, and add conditional formatting with custom formula

=match(A2, indirect("Sheet2!A2:A"), 0) > 0

Here match returns either the position of found element (a positive number) or #N/A, and the formula evaluates to True in the former case only.

Although the formula says "A2", it can be applied at once to any range that has A2 as its upper left corner.