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
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!