How do I create a formula for a Parent's "Assigned to" Inherit from Active Task
Hello!
I am using an agile template to plan sprints for my team. I'd like the Parent to take on the ACTIVE subtask 'assigned to' contact.
For example, the 'assigned to' column for the Parent would equal the sub-task's 'assigned to' column that is currently active (as indicated by 'stage').
Is this possible?
something like "IF CHILD stage = in progress, then PARENT assigned to equals CHILD assigned to"??
Best Answer
-
Hi @tcars!
You might try something like this: =IF(Stage@row = "In Progress", INDEX(COLLECT(CHILDREN(), CHILDREN(Stage@row), "In Progress"), 1)
This roughly translates to "If the Stage in this row is 'In progress,' bring back the value from the child row (from this column) where the Stage in that row is 'In Progress'. And, if there's more than one match, just bring the first one."
I hope this helps! Here are some articles about the functions I used that may be helpful:
- IF
- INDEX/COLLECT (this article is about cross-sheet references, but you can replace {range names} with [column name]:[column name] or, in this case, CHILDREN() references if you're using INDEX/COLLECT within the same sheet)
- INDEX
- COLLECT
- CHILDREN and Reference Children, Parents, and Ancestors with Hierarchy Functions
Best,
Heather
Answers
-
Hi @tcars!
You might try something like this: =IF(Stage@row = "In Progress", INDEX(COLLECT(CHILDREN(), CHILDREN(Stage@row), "In Progress"), 1)
This roughly translates to "If the Stage in this row is 'In progress,' bring back the value from the child row (from this column) where the Stage in that row is 'In Progress'. And, if there's more than one match, just bring the first one."
I hope this helps! Here are some articles about the functions I used that may be helpful:
- IF
- INDEX/COLLECT (this article is about cross-sheet references, but you can replace {range names} with [column name]:[column name] or, in this case, CHILDREN() references if you're using INDEX/COLLECT within the same sheet)
- INDEX
- COLLECT
- CHILDREN and Reference Children, Parents, and Ancestors with Hierarchy Functions
Best,
Heather
-
Amazing!! This is perfect. Thank you for helping me!
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 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!