Department Helper Column Rollup Multi Select

Dan Anzalone
edited 04/18/24 in Smartsheet Basics

The goal here is to create a helper column that with a formula will grab the department information from each task row, and then roll that up into the parent phase and project rows. However sometimes there is a department in the parent rows that are not in the task rows. So I need to be able to grab information from not only the child rows but also the current row as it rolls up. I also need the helper column information to be multi select.

The following formula does everything I need it to do except grab the parent row information as well.

=JOIN(DISTINCT(COLLECT(DESCENDANTS(Department@row), DESCENDANTS(Department@row), <>"")), CHAR(10))

Using the example data below the current formula will take dpt0, dpt1, and dp2 and put it into a helper column but at the project level the helper column wont include diff and i need it to include diff.

Could someone assist me with this? Thank you.



  • DKazatsky2
    DKazatsky2 ✭✭✭✭✭

    Hi @Dan Anzalone,

    This is happening because your formula is only taking descendants and the Project row is not a descendant. You should see this behavior in the Phase rows as well.

    Try this instead.

    =JOIN(DISTINCT(COLLECT(DESCENDANTS(Department@row), DESCENDANTS(Department@row), <>"")), CHAR(10)) + Department@row

    Hope this helps,


  • Dan Anzalone

    That new formula with the + Department@row did give me the additional value I was looking for but it did "break" CHAR(10) as the values don't seem to be split anymore and the + does add values without checking for them to be distinct from the original set that is being pulled in before the +.