Use Conditional Formatting in Google Sheets to highlight cell

0
votes

Problem

I want to be able to highlight a cell in google sheets if a cell on the same row is TRUE and the value does not exist in a named range (or a sheet).

e.g.

Based on the scenario below in sheet1, if column a is TRUE, and the value in column b does not match anything in sheet2, highlight the cell red. 

sheet1
column a     column b
TRUE         value1
FALSE        value2

sheet2 
column a 
value2

I would like to do this in google sheets using conditional formatting custom formulas and named ranges (if required).

Edit

I have tried this with my sheet but cant manage it... I have copied my sheet example below! Are you able to work it with this?

sheet1 enter image description here

sheet2 enter image description here

Edit 2

Rather than just matching whats in Cell D I would like to only match the values of the string that is separated by commas and only search if the letters "tf" are in the string.

1
regexcheckboxgoogle-sheetsgoogle-sheets-formulags-conditional-formatting

1 Answers

0
votes

if column a is TRUE, and the value in column b does not match anything in sheet2, highlight the cell red

try:

=(A1=TRUE)*(NOT(REGEXMATCH(B1&"", ""&TEXTJOIN("|", 1, INDIRECT("Sheet2!A:A")))))

4