How do I list projects from a specific department across multiple cells?

Swamini Vats

I have a Smartsheet where one sheet contains a list of projects categorized by departments. Each department has multiple projects listed under it. I need to extract and display all projects from a specific department in another sheet, with each project listed in its own cell vertically down the column.

I've tried using functions like JOIN and COLLECT in Smartsheet, but these functions seem to aggregate values into a single cell rather than listing each project in separate cells. Could someone please suggest a formula or method to achieve this? Your assistance would be greatly appreciated!

Brian_Richardson

If you have Data Mesh, you can use the Copy and Add function to create new rows in the second sheet when the projects are added to the first sheet.

You're right that formulas cannot send information into multiple rows.

What if you simply use reports to show the projects from a single department? If you have your projects organized hierarchically then you can add a column with the formula =PARENT([Project Name]@row) and make it a column formula…that will bring the Parent department name onto every project row. Then use a filter in a report to filter for that department.