Concatenate/Roll Up Text in Children Tasks
I want to rollup all of the updates made in the children tasks into the parent row. Is there a formula that can do that? Here is a screenshot of a parent row with some children tasks. The column with the updates is called 'Updates'. Can anyone help me create an IF statement that accomplishes this?
Answers
-
Hi @cvarela,
To concatenate the values from the children rows and do this only where a row is a parent row, you could add a new column and create a column formula similar to: =IF([IsParent]@row = 1, JOIN(CHILDREN(Updates@row), ", "))
Check out the following links for more information on the functions used within the formula:
- IF Function
- JOIN Function
- CHILDREN Function
- Reference children, parents, and ancestors with hierarchy functions
Is that what you’re looking for?
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi @Georgie, thank you. It is what I'm looking for, but the next question would be, how can we get it to not add "." for every child with a blank update? Here is what it looks like. I tested adding an update in each cell and the periods disappeared in favor of the text.
-
Hi @cvarela,
We can remove the blank child row values by adding in the COLLECT Function, telling it to only collect children row values where the cell isn’t blank. So, where your original formula is:
- =IF([IsParent]@row = 1, JOIN(CHILDREN(Updates@row), ", "))
Your final formula would be:
- =IF(IsParent@row = 1, JOIN(COLLECT(CHILDREN(Updates@row), CHILDREN(Updates@row), <>""), ", "))
The <> operator means “not equal to” , so <>”” means “not equal to blank” - more information on operators you can use in Smartsheet formulas can be found here: Create and edit formulas in Smartsheet.
Hope that helps!
Georgie
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!