Updating parent status from child rows in separate columns
Hello. I'm looking for a formula to update the Project Type column in a parent row to identify as "Digital" based upon specific contact entries in the Assigned To column in the child rows. For instance:
-child row Task 1: Assigned To Employee A
-child row Task 2: Assigned To Employee A, Employee B, and Employee C
-child row Task 3: Assigned To Employee B, Employee C, and Employee D
Employee A and Employee D=Digital employees (Employees B and C are not digital employees), so:
-Parent rows with any child rows assigned to Employee A or Employee D should appear as "Digital" in Project Type.
-Parent rows that do not have Employee A or Employee D assigned to any child row, the "Project Type" should remain blank.
Example below:
Thank you!
Best Answer
-
Hey @Taci Shinn
Try this
=IF(COUNTIFS(CHILDREN(Project@row), OR(HAS("Employee A", @cell), HAS("Employee D", @cell)))>0, "Digital")
If the children of your [Project Type] column have their own formula, you can automatically differentiate between the parent and child formulas
=IF(COUNT(CHILDREN(Project@row))>0, IF(COUNTIFS(CHILDREN(Project@row), OR(HAS("Employee A", @cell), HAS("Employee D", @cell)))>0, "Digital",<add Child [Project Type] column)) you may need more parentheses depending on your Child formula
cheers,
Kelly
Answers
-
Hey @Taci Shinn
Try this
=IF(COUNTIFS(CHILDREN(Project@row), OR(HAS("Employee A", @cell), HAS("Employee D", @cell)))>0, "Digital")
If the children of your [Project Type] column have their own formula, you can automatically differentiate between the parent and child formulas
=IF(COUNT(CHILDREN(Project@row))>0, IF(COUNTIFS(CHILDREN(Project@row), OR(HAS("Employee A", @cell), HAS("Employee D", @cell)))>0, "Digital",<add Child [Project Type] column)) you may need more parentheses depending on your Child formula
cheers,
Kelly
-
Thank you so much! I made one tweak to the column reference, but this works gloriously!
The tweak I made was to change the column from [Project] to [Assigned To]:
=IF(COUNTIFS(CHILDREN([Assigned To]@row), OR(HAS("Employee A", @cell), HAS("Employee D", @cell)))>0, "Digital")
Appreciate your help!
-
Actually, I made one additional tweak as the search range and criteria were transposed. Thanks again!
=IF(COUNTIFS(CHILDREN([Assigned To]@row), OR(HAS(@cell, "Employee A"), HAS(@cell, "Employee D")))>0, "Digital")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!