I'm attempting to apply conditional formatting if any two "paired" cells in two different ranges are both true.
The first range is on Sheet1, where the conditional formatting occurs. The second range is on Sheet2. However, the "pairing" is many to one, where the cells in a single Sheet1 column all map to one cell in Sheet2.
For example:
Sheet1
| A (conditionally formatted column) | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Value1 (formatting applies) | True | True | True | True |
| 2 | Value2 | False | False | False | False |
| 3 | Value3 | False | True | False | True |
| 4 | Value4 (formatting applies) | True | False | False | False |
| 5 | Value5 (formatting applies) | False | True | True | False |
Sheet2
| G | H | I | J | |
|---|---|---|---|---|
| 1 | True | False | True | False |
I'm currently using the following custom formula, and setting the conditional formatting range to A:A on Sheet1.
=OR(AND(B1, INDIRECT("Sheet2!G1")), AND(C1, INDIRECT("Sheet2!H1")), AND(D1, INDIRECT("Sheet2!I1")), AND(E1, INDIRECT("Sheet2!J1")))
The formula works for this example, but in reality I have 35 columns (and more to come). I'm looking for a more sustainable solution. Is there a way to use ranges in my example custom formula, instead of a series of AND conditions?
CodePudding user response：
How about
=ArrayFormula(sum(B1:E1*indirect("sheet2!g1:j1")))
Or shorter
=SUMPRODUCT(B1:E1*indirect("sheet2!g1:j1"))
CodePudding user response：
try:
=(((B1=INDIRECT("Sheet2!G1")) (C1=INDIRECT("Sheet2!H1")) (D1=INDIRECT("Sheet2!I1")) (E1=INDIRECT("Sheet2!J1")))>1)*(A1<>"")
