Automated adding in project
Hello,
I have a portfolio of about 80 projects, each with their own project sheet and all built on the same template. My organization has several functional groups (lab operations, procurement etc.) that work on their portion of these projects. These functional groups would like to start using smart sheets to manage their operations. My question is how can I pull project data from the portfolio project sheet (for example - project manager, client, current stage, etc.) into a sheet designed specifically for the functional area. For example - Lab Ops is working on Project 1. When I list project 1, I would like to auto populate several fields tied to that project.
I was thinking I could build a master portfolio sheet that pulls the specified information from each portfolio sheet then use a series of INDEX(MATCH) Statements to auto-populate the functional area sheet with the necessary information.
This would be a lot of work to do manually. Is there a way to automatically link the data all of the sheets in a Workspace to the master portfolio sheet? I do not have DataMesh and sadly, it is not possible to use data pulled from reports.
Thank you
Answers
-
Have you looked into creating row reports?
-
Hi @TCJ
I hope you're well and safe!
Not sure I follow. Can you elaborate? Wouldn't Paul's suggestion of using reports work?
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hello,
Thanks for considering my questions. A row or summary column is able to pull the data into a single place, BUT, it is not actionable. I need the data to from the report to be pulled into a sheet, where additional columns can be added. Individually linking each sheet and cell is way to much work. Below is a graphic that might help.
-
@TCJ It sounds to me like you have the perfect use case for the Control Center add-on. Without it, you are unfortunately looking at a rather manual process where cell links and/or cross sheet references in formulas need to be created individually for each project as it is provisioned.
-
Thank you Paul, I kind of suspected as much. Of course, there is a pretty big price tag for Control Center. I think I have a bit of a workaround, where I pull all of the necessary data from project sheets into a report, then export the report to Excel or Google sheet, then import that same report into a Smartsheet grid, which will allow me to use Index(Match) functions to populate the Department/Functional Area sheet. I can run the export/import once a week, and while not linked, it should fill the purpose. This might be something I can write a small script to automate. Not a great solution. A bit disappointed that there is not an easier solution. Thanks again for your help
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!