Hihglight duplicate cells from two different sheets in Google Sheets

1
votes

I am trying to highlight duplicate cells of Column A in sheet 1 by matching Column A in sheet 2. I used this formula in conditional formatting in sheet 1. 

=countif(INDIRECT("2!A:A"),A1)>1

But it not working.

In sheet 1 Column A has these items

enter image description here

In sheet 2 Column A has these items

enter image description here

Here is the formula

enter image description here

Here is the google sheet

I check other links, but couldn't complete the task. So I am posting a new post.

1
google-sheetsgoogle-sheets-formulacountifgs-conditional-formatting

1 Answers

1
votes

your formula should be:

=COUNTIF(INDIRECT("2!A:A"),A1)>0

0