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