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.
You can make the formula easier to follow by calculating the date once and then deciding whether that month is active. If the checkbox is linked to AF36 and returns TRUE/FALSE, try this Microsoft 365 formula:
=LET(pdd,IFERROR(--$M36,0),active,AND($H36<>"",OR(pdd=0,N$6<pdd)),IF(active,IF($AF36,"O/H",1),""))
Enter it in the first month cell and fill it across the 18-month range. The logic is: H36 must contain a value; a blank or invalid PDD means the month remains active; a real PDD blanks months on or after that date; and the checkbox changes only an active result from 1 to O/H. This avoids calling EDATE on a blank, a space, or text, which is what produces the #VALUE! result. Also confirm that AF36 contains the Boolean value TRUE or FALSE rather than the text “TRUE” or “FALSE.” Using LET here makes each condition visible and prevents the checkbox branch from being skipped inside a nested IF.