Search code examples
excelspreadsheetconditional-formatting

Excel Conditional Formatting with 2 conditions across more than 1 sheet

I have a very simple setup:

Sheet 1

A B C D E
1 56.00% 56.00% 87.00% 76.00%

Sheet 2

A B C
1 0 1 2

What I want is to have all cells in Sheet1 in columns A through C whose value is less than Column E turn Red ONLY IF the corresponding cell in Sheet 2 is greater than 0.

So, the expected result would be that only Cell B1 would turn red.

I am using this formula: =AND($A$1 < $E$1,Sheet2!$A$1 >0) applies to =$A$1:$C$1

But it doesn't work (none of the cells change color).

enter image description here

What am I doing wrong?

Solution

  • You need to use an absolute reference for E1 but no other cells

    =AND(A1 < $E$1,Sheet2!A1 >0)

    Should work

    (with your current conditions, the condition would apply to all cells if Sheet2!A1 was greater than zero)