Using file (sheet) name to look up and summarize multiple sheets?
Is it possible to create a summary sheet that uses a sheet name (or other unique identifier) lookup to pull data from a source file into a summary file?
I have a portfolio of projects in a workspace with a template folder for each project, which includes a "Project Summary" sheet, and a "Portfolio Summary" sheet that pulls date from each project's "Project Summary" data sheet. Some of the Project Summary data pulls from other sheets in the project template, some is entered manually. All of the data in the Project Summary sheets is stored in rows, whereas the Portfolio Summary is organized by columns (so I can't just copy a whole row from the source Sheet and paste into the destination sheet). For example:
Source Sheet (Project Summary):
Destination Sheet (Porfolio Summary):
To create the Portfolio Summary sheet, we are using "Link from Cell in Other Sheet..." and manually making the connections between the Portfolio Summary and each individual Project Summary to pull data from the latter to the former.
This method is A.) a time suck (technical term) and B.) prone to error (all those connections!!!).
To expedite this process and reduce exposure to errors, I would LOVE to build a procedure in my Portfolio Summary sheet that, provided a unique common identifier (project name? link to respective project's summary sheet?), goes to the respective Project Summary sheet and pulls the targeted data.
Is this possible?
Help Article Resources
Check out the Formula Handbook template!