Compressing based on a column label different row data points in one cell

Adrià Termes

Hi all.

I am trying to do a rather complex operation on Smartsheet, generally performed in programming. I have two different sheets: one for Departments and another one for teams. Both sheets can be linked through the interrelation they have, although the teams sheet have multiple times the department across rows (E-G: Finance appears in multiple rows: Cost Accounting team, Financial Forecasting team... etc., all of them with the label "Finance" in the Department column).

I would like, moving on to the Department sheets, to have all the different departments either as a dropdown list or strings inside a specific cell. Basically speaking, I want to compress the different teams inside each department in one cell so that the rows are flattened to Department-level data. Below I attach a graphic example of what I would like to have, with the Departments Sheet having the Teams row compression mentioned.

Is there any way to do this, so that we can have such compressed cell, even in a dropdown fashion? I have not been able to find a proper solution to it, and if you have a clue I would gladly explore it further.

Thank you!

Matthew J McAteer

Hi @Adrià Termes

You can do this using a cross sheet reference.

=JOIN(COLLECT({TeamTeam}, {TeamDepartment}, Department@row), ", ")

TeamTeam references the Team column on the Team sheet, and TeamDepartment references the Department column on the Team sheet.

Hope this helps.

Matthew

Matthew J McAteer

Hi @Adrià Termes

You can do this using a cross sheet reference.

=JOIN(COLLECT({TeamTeam}, {TeamDepartment}, Department@row), ", ")

TeamTeam references the Team column on the Team sheet, and TeamDepartment references the Department column on the Team sheet.

Hope this helps.

Matthew

Adrià Termes

Excellent @Matthew J McAteer ! I just saw that formula before and it did not work as I was literally choosing the wrong order all the time. Now it is clear and functioning thanks to your comment.

Huge props to you!

Adrià Termes

Matthew J McAteer

Happy to help 😊