Combine Info from Multiple Projects to a Report, pull status from Report to Sumamry Sheet

First, forgive me if this is convoluted, I may be approaching this in entirely the wrong manner.

I have a varying number of workspaces, one workspace for each project. The number of projects changes over time. I currently provide a "Master Project Summary" of the status of all projects by using a report and selecting a "Project Data" sheet contained in each workspace. This requires me to add every "Project Data" sheet to the "Master Summary" report, but I only have to do that once (at project initiation). The result is a report with project numbers followed by columns for each major milestone.

Separately, we have a "Master Sales Record" sheet of all sales opportunities. Some have become active projects, some are still in the sales process, and some are complete. We would like to pull the project status from the "Master Project Summary" report into the "Master Sales Record" - without having to write a new formula that reference the corresponding "Project Data" sheet for every entry. If vlookup with cross reference could use my "Master Project Summary" report - that would solve the problem.

I hope that make sense below is a graphic of what I want


Best Answers

  • Julio S.
    Julio S. Moderator
    Answer ✓

    Hi @mattstoc,

    Because Reports don't actually store the data they reference, they can't be used as a source in cross sheet references. As you say, creating VLOOKUP formulas in the Sales Summary sheet could be an option, but this would require creating new references for each new project sheet that is created. This could be a good idea if the Project Master item was a sheet instead of a Report. You could possibly automate this task by creating Copy row workflows in each Project sheet to copy the data into a Master sheet that can the be used as the source for your formula.

    An alternative option would be to use cell-links from each project status cell to the Sales Summary sheet. This would also be a manual task for each new project inserted but would avoid the task of creating new versions of the formula for each instance. Please have a look at Cell Linking if this sounds like an option.

    I hope this can be of help.

    Cheers!

    Julio

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    Hi @mattstoc

    I hope you're well and safe!

    It seems that a solution that would be perfect for this use case is the premium add-on, Control Center.

    Is that an option?

    More info: 

    I hope that helps!

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

Answers