Forum Discussion
Nested IF function with a checkbox
Hello GregPen,
That clarification makes the intended logic clearer.
The checkbox should only change a cell that would otherwise contain 1. It should not override cells that the date logic would leave blank.
Try:
=IF($H36="","",IF($M36="",IF($AF36,"O/H",1),IF(N$6>=EDATE($M36,0),"",IF($AF36,"O/H",1))))
The key is that AF36 is checked only after the formula determines that the result for that month should be 1.
The logic is:
H36 is blank → blank
M36 is blank → 1, or O/H if AF36 is TRUE
N6 is on or after the PDD date → blank
Otherwise → 1, or O/H if AF36 is TRUE
This keeps the existing date cutoff while replacing only the 1s with O/H when the checkbox is selected.
It also keeps the AF36 test out of the EDATE calculation when M36 is blank, avoiding the #VALUE! problem.
Hello GregPen,
The key is to decide which condition has priority.
If checking AF36 should always return O/H, regardless of whether M36 contains a date, then the checkbox test needs to be evaluated before the M36 test:
=IF($H36="","",IF($AF36,"O/H",IF($M36="",1,IF(N$6>=EDATE($M36,0),"",1))))
With this order, Excel evaluates:
H36 blank → blank
AF36 checked → O/H
M36 blank → 1
Otherwise → apply the date comparison
The original formula only checks AF36 when M36 is blank. Once M36 contains a date, Excel follows the other branch and AF36 is never evaluated.
If O/H is supposed to depend on both the checkbox and the date condition, then the checkbox needs to be incorporated into that date branch instead.
This kind of works in that it will display O/H across the entire row, however, I only need it to show O/H if there is a 1. I think I need an AND statement that says if there is a "1" and the checkbox is TRUE, then display O/H, otherwise leave it blank.