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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
  • Andrée Starå
    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!

    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.

  • TCJ
    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
    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
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!