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
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!