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

Upvotes: 0

Answers (3)

player0

Reputation: 1

try:

=MATCH(B1, FILTER(INDIRECT("Completed!C:C"), INDIRECT("Completed!A:A")=TRUE), 0)

Upvotes: 1

basic

Reputation: 11968

Use:

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

Upvotes: 2