Department Helper Column Rollup Multi Select
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.
Answers
-
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,
Dave
-
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 +.
