Join multiple ranges when using conditional formatting

Question

I have a simple conditional formatting custom formula which highlights duplicate values in a column:

=countif(A:A,A1)>1

I have another column on another sheet (same workbook) which I would like to include in the search for duplicates. I can combine the ranges separately using the following formula:

=query({sheet1!A:A; sheet2!A:A},"where Col1 <>''")

but I can't get that formula to work in conditional formatting. What I have (which doesn't work), is:

=countif(query({sheet1!A:A; sheet2!A:A},"where Col1 <>''"),A1)>1

I don't understand how "where Col1 <>''" works or why I need it, but even so, the formula doesn't work.

The question is, how can I (using conditional formatting) highlight duplicate cells in column A, searching across multiple ranges for duplicates?

Answer 1

• when referencing another sheet in conditional formatting, you need to use INDIRECT

• conditional format rules are per sheet meaning that you need 1 separate rule per every sheet

• "where Col1 <>''" means to select only those rows from a query which are not empty in the 1st column

• to show duplicates between 2 ranges use this custom formula:

  =COUNTIF({A:A,INDIRECT("Sheet2!A:A")},A1)>1