excelexcel-formula

Conditional formatting formula based on value in other sheet


I use this formula in condition formatting to change the row color red based on matching value between 2 sheets in the same workbook =SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)*('Stop Work'!$K$8:$K$500="Open"))

the formula says (if "D" in "stop work" sheet = any cell of "C" in this sheet and "K" in "stop work" sheet = "open" then color the row in red

and because this rule is repeated in many sheets in the same workbook and some cells value can be the same in other sheets so I get wrong result in other sheet that shouldn't be colored so I try to add another condition to make the formula apply in this sheet only (for example if (if "D" in "stop work" sheet = any cell of "C" in this sheet and "K" in "stop work" sheet = "open" and check if the adjacent cell "E10" in stop work sheet = ($A$1) in this sheet) then color the row

I hope I could expalined it well

thank you in advance


Solution

  • I have tried this formula and it works

    =SUMPRODUCT(('Stop Work'!$D$8:$D$500=$C10)('Stop Work'!$K$8:$K$500="Open")('Stop Work'!$C$8:$C$500=$B$2))

    thank you anyway