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.Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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.Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!