Automated adding in project

TCJ

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

Paul Newcome

Have you looked into creating row reports?

Andrée Starå

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!

TCJ

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.

Paul Newcome

@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.

TCJ

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