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.
It works when all 18 months contain 1 because, in that case, M36 is blank and the formula reaches IF($AF36,"O/H",1).
When fewer months contain 1, M36 contains a date, so Excel uses this branch instead:
IF(N$6>=EDATE($M36,0),"",1)
That branch never checks AF36, which is why the remaining 1s do not change to O/H.
Use this formula instead:
=IF($H36="","",IF(AND($M36<>"",N$6>=EDATE($M36,0)),"",IF($AF36,"O/H",1)))
- Enter it in the first month cell.
- Copy it across all 18 months.
- Select the checkbox linked to AF36.
Months beyond the date cutoff will remain blank. Any month that would normally display 1 will change to O/H when the checkbox is selected.
This formula kind of works as it changes the 1s to O/H as long as there is a date in the PDD block. If there is no date then all the cells get a VALUE error. I think the AND function is needed at the end to check that if there is a "1" AND the box is checked, then put "O/H".
This works if there is a date in the PDD cell, however, if there is no date, then I get a VALUE error. As I stated in the comment below, I think your AND statement is necessary but needs to be that if the the box is checked and there is a "1", then display O/H, otherwise leave it blank (maybe at the end as a final check?).
This formula didn't work and if it's in a row that doesn't have an OUTBOUND PDD, I get a value error.
