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.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!