Copying multiple values from one column to a new sheet

sophiaashepard

Hi all,

We are using Smartsheet to forecast staff LOEs across projects. We currently have a Data Input sheet where staff LOEs are entered by project (nested by Center). We then have another sheet, Enterprise Rollup, which searches for staff names and adds up their LOEs across projects to calculate their forecasted direct billability across all projects. I am hoping to be able to do something similar with searching for a staff name and instead of adding up LOEs, it would copy over the project names from each row that the staff name is found to develop a list of projects in one column. I created an extra column that copies the project name from the parent column to the children columns so there is a project name associated with each row. To summarize the outcome, I would like for 'Projects' in the Enterprise Rollup to list each project the staff member works on based off the Data Input sheet. For example, for Amanda, her 'Projects' would be listed as "ASAM CJ, HRSA MCHB PMHCA" in the blank 'Projects' column in the Enterprise rollup.

Data Input Sheet:

Enterprise Rollup sheet:

Isaac A.

Hi @sophiaashepard!

I tested this out and used the following formula to accomplish what you're trying to do:

=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ")

You’ll need to place this formula in your Enterprise Rollup sheet, specifically in the 'Projects' column. Just make sure to create the cross-sheet references for the Project CHEAT and Person columns from your Data Input sheet.

For more information on cross-sheet formulas, you can check out this helpful article: Cross-sheet formulas.

Give it a try, and let me know if it works!

Cheers,
Isaac.

Isaac A.

Hi @sophiaashepard!

I tested this out and used the following formula to accomplish what you're trying to do:

=JOIN(COLLECT({Project CHEAT}, {Person}, Person@row), ", ")

You’ll need to place this formula in your Enterprise Rollup sheet, specifically in the 'Projects' column. Just make sure to create the cross-sheet references for the Project CHEAT and Person columns from your Data Input sheet.

For more information on cross-sheet formulas, you can check out this helpful article: Cross-sheet formulas.

Give it a try, and let me know if it works!

Cheers,
Isaac.