IF/AND formula with Children

I am trying to create a formula that returns different values based on if a value is in one of the children cells and if a checkbox is checked.

So what I want to happen is if the checkbox is not checked in the row for "F/T Cycle 3", then return the value "F/T due", and if it is checked return "F/T complete". I got the formula to work if I put in the specific cells, but not if I try to put in the CHILDREN formulas so I can make this a column formula. I think I am messing up the syntax somewhere?

This formula works but cannot be used as a column formula:

=IF(AND([Evaluation Type]343 = "F/T Cycle 3", [Evaluation Completed]343 = 0), "F/T Due", "F/T Complete"

These do not work:

=IF(AND((CHILDREN([Evaluation Type]@row)) = "F/T Cycle 3", (CHILDREN([Evaluation Completed]@row) = 0)), "F/T Due", "F/T Complete")

=IF(AND(CHILDREN([Evaluation Type]@row) = "F/T Cycle 3", CHILDREN([Evaluation Completed]@row) = 0), "F/T Due", "F/T Complete")



Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭

    @Cpatt

    Try this but it may need further tweaking for criteria.

    =IF(COUNT(CHILDREN([Formula #]@row)) > 0, "", IF(AND(COUNT(ANCESTORS([Formula #]@row)) > 0, [Evaluation Type]@row = "F/T Cycle 3", [Evaluation Completed]@row = 0), "F/T Due", "F/T Complete"))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!