0

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

player0
  • 124,011
  • 12
  • 67
  • 124
hardy
  • 537
  • 1
  • 5
  • 19

2 Answers2

2

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

David_cav
  • 81
  • 5
1

if your Sheet1 looks like this:

0


then all you need is:

=INDIRECT("Sheet1!M2:M")="Done"

enter image description here

spreadsheet demo

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124