Search code examples
regexgoogle-sheetsfilterconditional-formattingtextjoin

Conditional Formatting: multiple conditions with indirect references

I want to have text on a cell of one sheet highlighted if and only if there is a same string on another sheet AND the checkbox next to it is marked true:

Sheet 1 (the current sheet that I'm doing conditional formatting on) contains "UNIQUETEXT751" on B column

Sheet 2 (named "Completed") contains "UNIQUETEXT751" on C column AND the checkbox on A column is marked true

It should highlight the cell on Sheet 1 with "UNIQUETEXT751"

These are the following things I've tried...

=match(B2:B,INDIRECT("Completed!C2:C"),0)*(INDIRECT("Completed!A2:A")=true)

=match(B2:B,INDIRECT("Completed!C2:C"),0)*(COUNTIF(INDIRECT("Completed!$A:$A")),true)

=AND(match(B2:B,INDIRECT("Completed!C2:C"),0),(COUNTIF(INDIRECT("Completed!$A:$A"),$A2)))

None of them seems to work.

EDIT: Here's a test sheet with examples. https://docs.google.com/spreadsheets/d/1CI190i9N6EACVgITS4sVcz8lWN310cWt1UTdg07A0s0/edit#gid=0

Solution

  • Use:

    =COUNTIFS(INDIRECT("Completed!C:C"),B1,INDIRECT("Completed!A:A"),TRUE)

    enter image description here