Copying multiple values from one column to a new sheet
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:
Best Answer
-
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.Join us at Smartsheet ENGAGE 2024🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
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.Join us at Smartsheet ENGAGE 2024🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!