0

I know how to apply conditional formatting if there are duplicate values in a column and I know how to use a formula to that references values from multiple sheets; however, what I want to do is apply formatting to a value if it is a duplicate in another sheet.

Example: I have sheets "friendlies", "healthcare", "IT" and "marketing" that contain names and contact information. Email addresses are always in column E.

Occasionally someone gets moved from a vertical, healthcare for instance, into the friendlies sheet.

When someone gets added to the friendlies worksheet, I want their email cell (in column E) in the friendlies sheet to turn red to remind me to remove them from the healthcare sheet.

Is it possible create a formula that looks to another sheet for a duplicate? I tried the below with no luck:

=countif(Healthcare!E:E,E1)>1, as well as =countif(Healthcare!E:Healthcare!E,E1)>1 and then finally =countif(Healthcare!E:Healthcare!E,Healthcare!E1)>1

Does anyone have any insights to help?

frog-baby
  • 1
  • 1
  • 1
  • Have you tried to incorporate `AND()`? – tehhowch Mar 14 '18 at 20:01
  • The scenario seems rather convoluted. Can you provide a minimal example of what you are trying to accomplish? In any case, check the documentation of [`match`](https://support.google.com/docs/answer/3093378) to see if you can come up with a solution yourself. – Argyll Mar 14 '18 at 23:47

1 Answers1

0

Presumably in connection with security, conditional formatting across sheets can be a bit of a rigmarole but the CF formula rule is very simple if you are prepared to make use of a helper column.

Taking a similar approach to what you tried, the helper column, say F should be populated with, in say F1, and copied down to suit:

 =countif(healthcare!E:E,E1)+countif(IT!E:E,E1)+countif(marketing!E:E,E1)

Then it is just a matter of selecting ColumnE in friendlies and Format - Conditional formatting..., Custom formula is and: 

=F1>0

with selecting red formatting of choice and Done.

An alternative to a helper column is to apply named ranges and INDIRECT:

=countif(indirect("hMail"),E1)+countif(indirect("imail"),E1)+countif(indirect("mMail"),E1)

hMail for example the name for ColumnE in healthcare.

pnuts
  • 58,317
  • 11
  • 87
  • 139