How to Apply Conditional Formatting based on other sheet value

Question

I have a sheet with data, in Sheet1 I have data in column M (Text data value "Done") so now I try to apply Conditional Formatting on my Sheet2 column K2:K if Sheet1 Column M have value "Done". I tried this formula:

=Search("Done",indirect("Sheet1!M2:M"),0) 

also this one:

=search("Done",Sheet1!$M2:$M)   

but not working

Answer 1

Assuming that you don't want to highlight the entire column but just on a line-by-line basis, could you try with the following formula :

=INDIRECT("Sheet1!"&CELL("address",M1))="Done"

It worked for me when applying this as a conditional formatting rule in column K of sheet 2 and adding manually-created sample data in column K of sheet 1.

You might also want to check this if you were expecting another behaviour : Conditional Formatting from another sheet