google-sheetsgoogle-sheets-formula

How to use conditional formatting to change color based on an array of strings in a different sheet

I have a workflow for contacting businesses, which I am trying to improve.

"Sheet1" contains information on businesses that have been contacted for "X" purpose. If there was success in contacting a particular business, it turns green conditionally, based on whether the "status" of that contact was a "hit" or a "miss."

Sheet1

Any successful leads on a business; that is... any "hits" are transferred to "Sheet2." "Sheet2" does a Query of "Sheet1," taking all values from any row that returns "hit" under the "status column." In my example, there are only two companies that are returned.

Sheet2

The equation I use for "Sheet2"

Now, what I am trying to do involves "Sheet3." In "Sheet3," I have a series of storage facilities located in the areas where these businesses are being contacted. If I have a green row returned in "Sheet2"—that is, if I have a "hit"—then I would like every row in "Sheet3" with the same "State Initial" to turn green as well.

Sheet3

This is how I would like it to look.

What I would like Sheet3 to look like

Here is the link to the sheet: https://docs.google.com/spreadsheets/d/1cMvrg1SBl3tGYaPmsAufyYyDfUMrXA8sMPDrAc3jBrg/edit?usp=sharing

Solution

  • Use COUNTIF

    Highlight the range A4:E10 in Sheet3 then go to Format > Conditional formatting > Add another rule.

    In the Format Rules section, choose Custom formula is and enter the formula below:

    =AND(COUNTIF(INDIRECT("Sheet2!$E:$E"), $E4)>0, COUNTIF($E$4:$E$10, $E4)>1)

    References: