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

mattstoc

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

Julio S.

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å

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: 

https://www.smartsheet.com/marketplace/premium-apps/control-center

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!

Julio S.

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å

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: 

https://www.smartsheet.com/marketplace/premium-apps/control-center

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!

mattstoc

Thank you both for the information.

Julio - I was unaware of cell-linking and they maybe a workable solution, I need to play with it a bit and see.

Andree - The control center looks very useful, I'm jut not quite ready to invest in a another premium tool. However, I may be as our portfolio grows.

Andrée Starå

@mattstoc

You're more than welcome!

Let me know if you'd like to explore Control Center, and I'd be happy to help!