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

user_18970485 0
Viewed 48

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.

Here's the example sheet.

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.

google-sheets-formula google-sheets
1 Answers

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))
user_15277800
Related