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
-
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
Categories
Check out the Formula Handbook template!