CODEWITHSUNDEEP
google-sheets
Jackson Cunningham
Jackson Cunningham

Reputation: 5073

Google Sheets: Conditional Formatting IF cell is a duplicate AND is not empty

I have a column with website URLs. I'd like to highlight them in red if they are a duplicate AND are not empty.

Here's my current approach, applied to the column range:

=AND(countif(E:E,E1)>1,not(ISBLANK(E:E)))

The countif part will find duplicates. But I'm not sure what should be going in between in ISBLANK() part. How do I refer to "this" cell?

How do I approach this?

Upvotes: 2

Views: 1622

Answers (1)

Jack Brown
Jack Brown

Reputation: 5892

=And((countif(E:E,E1)>1),not(isBlank(E1)))

refer to the cell as E1 like you do in countif.

Another way to refer to current cell is like this: 

=INDIRECT(address(row(),column())

So you can modify the above condition to this:

=And((countif(E:E,INDIRECT(address(row(),column())))>1),not(isBlank(INDIRECT(address(row(),column())))))

Upvotes: 4

Related Questions