How to manage resource loading from a master sheet.



I trying to create a master sheet of a set of projects in order to help with resource loading questions. I can easily create a report that displays all the tasks for employee A vs employee B. I can create a report on all tasks across all selected projects with employees A-E. However, since we cannot use formulas in the reports to manipulate the data for widgets in dashboards (Example: a graph of employee A's open, closed, and in progress tasks) and other needs, the reports are limited to being just a series of collated lists. I have therefore created a master sheet by linking every relevant cell in each project in single location, then creating a series of calculations rows for formulas in the master sheet, and then creating widgets off of the calculations to present to upper management. The problem is that any time there is a scope change (which is often) and a project manager needs to add tasks to a single project, I cannot find a way to easily update the master sheet. I had high hopes for the "copy row" workflow, but it does not update into, it only adds to the list. I end up finding all rows in the master sheet for a project that has changed, deleting them in their entirety, and then relinking the entire project again in the master sheet. Does anyone have an easier way that doesn't lend itself to human error?



Best Answer


  • Claire Mowry

    Thank you. I was not approaching the problem from the angle. I'm receiving either a circular reference or #unparseable. I assume it has to do how I link to a Mastersheet.

    This screen shot is from a Project Called: "20-011 R&D Project - Standard Filter Proj (PRV)".

    Then when I link from a Master sheet to the project sheet, I clicked on the column "Metrics Helper" or a cell in that column. The result is Unparseable.

    Can you see where I made a mistake?

    Thank you!!!