I am working with a client that wants one sheet to be a master list of all projects the team is working on. Whenever a new project is added to the sheet, the project owner creates a separate Project Plan sheet, with list of tasks, deliverables, Assigned to, and Start/End/Due Dates, and Status.
This is a manual process (creating the new Project Plan sheets).
The client would like the one sheet listing all of the projects to show the status of the project (based on the % of tasks completed —- that is tasks in the Project Plan sheet that are marked "Completed"). I do not think they will do anything else in the sheet but just view the status of projects and "dig deeper" into the Planning Sheet if there is any issue.
My initial thought is a cell reference formula to a summary field in the Planning Sheet.
However, this cell reference would need to be added manually any time a new project is added and a new Project Planning sheet is created.
I feel like I would be asking the client to do work (creating the cell reference formulas) every time a new project is started (and a new Planning Sheet is created).
Would a report be better, that automatically includes every Project Planning Sheet saved within a certain folder in the workspace? This way, no cell reference formula is needed, and the new project planning sheets would automatically be added once they are saved in the same folder.
Is my thinking correct in terms of how others would do it? Or is there a way to use a sheet without asking the client to create new formula over time?
