I have checkboxes in Column I
and the following conditional formatting rule:
<code>=AND($I13=TRUE,$A13<>"")
</code>
<code>=AND($I13=TRUE,$A13<>"")
</code>
=AND($I13=TRUE,$A13<>"")
The rule is correctly applied to Row 13
when I13
is checked, but when I14
and I15
are checked it isn’t applied to Rows 14
and 15
.
What could be causing this?
2
Try any of the following:
<code>=$I13 * LEN($A13)
=$I13 * ($A13<>"")
=AND($I13, LEN($A13))
=AND($I13, $A13<>"")
</code>
<code>=$I13 * LEN($A13)
=$I13 * ($A13<>"")
=AND($I13, LEN($A13))
=AND($I13, $A13<>"")
</code>
=$I13 * LEN($A13)
=$I13 * ($A13<>"")
=AND($I13, LEN($A13))
=AND($I13, $A13<>"")
If the formulas above return FALSE for any row and Column I is checked, then the two most likely explanations are:
- Column A is not empty.
You can test a range of cells to check if they’re empty using the following formula. Modify the rangerng
to fit your needs:<code>=INDEX(LET(rng,A14:A20,IF(LEN(rng),ADDRESS(ROW(rng), COLUMN(rng), 4) & " not empty",)))</code><code>=INDEX(LET(rng,A14:A20, IF(LEN(rng), ADDRESS(ROW(rng), COLUMN(rng), 4) & " not empty",))) </code>=INDEX(LET(rng,A14:A20, IF(LEN(rng), ADDRESS(ROW(rng), COLUMN(rng), 4) & " not empty",)))
- Conditional format range mismatch
If your conditional format custom formula is:<code> =$I13 * LEN($A13)</code><code> =$I13 * LEN($A13) </code>=$I13 * LEN($A13)
The range of the rule should also start in Row 13. For example,
<code>=A13:I</code><code>=A13:I </code>=A13:I
1