Conditional Formatting: multiple conditions with indirect references

Question

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

@player0, I have just edited in an example sheet that you can play around with. Here's the link: https://docs.google.com/spreadsheets/d/1CI190i9N6EACVgITS4sVcz8lWN310cWt1UTdg07A0s0/edit#gid=0 — Brandon Park, Oct 22 '21 at 19:11

basic · Accepted Answer · 2021-10-22T19:44:22.497

2

Use:

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

edited Oct 22 '21 at 19:44

answered Oct 22 '21 at 18:48

basic

11,673
2
9
26

Thanks for your help. It does not seem to work, however; none of the cells get highlighted. I definitely have some of these strings in Completed sheet with the checkbox checked, so I don't know what's wrong... – Brandon Park Oct 22 '21 at 19:05
@BrandonPark Yeah, my mistake. See edited answer. – basic Oct 22 '21 at 19:45
Perfect, thank you for your assistance. Works perfectly, and is faster than player0's answer. – Brandon Park Oct 22 '21 at 20:01

score 1 · Answer 2 · answered Oct 22 '21 at 19:18

1

try:

=INDEX(REGEXMATCH(B1, TEXTJOIN("|", 1, 
 FILTER(INDIRECT("Completed!C:C"), INDIRECT("Completed!A:A")=TRUE))))

answered Oct 22 '21 at 19:18

player0

124,011
12
67
124

Works perfectly. Can you please explain how it works so I understand what's going on? – Brandon Park Oct 22 '21 at 19:22
@BrandonPark sure. so we use INDIRECT for ranges which are on other sheet. then we use FILTER to get only those values which have checkbox checked. next we join all those filtered values with pipe | symbol (meaning or) in TEXTJOIN and lastly we REGEXMATCH those matches in B column. we are processing arrays so we use INDEX – player0 Oct 22 '21 at 19:26
If filter will allow us to filter out all the ones with checkbox checked, do we actually need to join them and lay it out across an array? I was under the assumption that conditional formatting checks 1 line at a time so an array is not necessary? – Brandon Park Oct 22 '21 at 19:29
Additionally, this conditional formatting actually seems kind of slow. My sheet is perpetually loading formula whenever I swap between tabs and edit cells. – Brandon Park Oct 22 '21 at 19:36
@BrandonPark there are many solutions. this is just one of them. I added another which is faster. – player0 Oct 22 '21 at 20:09

score 1 · Answer 3 · answered Oct 22 '21 at 20:07

1

try:

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

answered Oct 22 '21 at 20:07

player0

124,011
12
67
124

Conditional Formatting: multiple conditions with indirect references

3 Answers3