Conditional formatting, if A2<B2 and D2<E2 on Sheet1, then cell is green on Sheet2-CodePudding

I'm trying to do as titled.

Product on Sheet2 will only be coloured green if both A2<B2 and D2<E2 on Sheet1.
If one of the criteria isn't fulfilled, the product will not be green.

I tried this formula, but either there's no effect, or I get an invalid code error.

=AND(INDIRECT("'Source Data'!A2<'Source Data'!B2","'Source Data'!D2<'Source Data'!E2")

Is there something wrong with the formula? Thanks in advance.

CodePudding user response：

You asked:

Is there something wrong with the formula?

Yes. You are using INDIRECT the wrong way. Please read more about INDIRECT

For your given data you can use

=AND(INDIRECT("'Source Data'!A"&ROW()-2)<INDIRECT("'Source Data'!B"&ROW()-2), 
     INDIRECT("'Source Data'!D"&ROW()-2)<INDIRECT("'Source Data'!E"&ROW()-2))