How to manage resource loading from a master sheet.

Claire Mowry

Hello,

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?

Thanks!

Claire

Ramzi K

@Claire Mowry

Many ways to do it. Here is one suggestion.

In each project sheet, create a helper column (call it Metrics Helper) with the column formula:

=[Assigned To]@row + ">" + Status@row

Should look like this:

Then in your "master sheet" create columns and rows like this:

One top reference row to make formulas more dynamic (but you can also hard-code for each column. And a column for each status and one for the list of resources (of Type Contact List)

Should look like this:

The formulas in the count cells would look like this with external sheet references back to all the project sheets:

=COUNTIFS({Project 1 Metrics}, $Resource@row + ">" + [Not Started]$1) + COUNTIFS({Project 2 Metrics}, $Resource@row + ">" + [Not Started]$1) + COUNTIFS({Project 3 Metrics}, $Resource@row + ">" + [Not Started]$1)

Now, when you add tasks to the projects and assign them, your counts will be dynamic.

I hope this makes sense and helps you. Again, one of many ways you can achieve what you want.

Cheers,

Ramzi

Ramzi K

@Claire Mowry

Many ways to do it. Here is one suggestion.

In each project sheet, create a helper column (call it Metrics Helper) with the column formula:

=[Assigned To]@row + ">" + Status@row

Should look like this:

Then in your "master sheet" create columns and rows like this:

One top reference row to make formulas more dynamic (but you can also hard-code for each column. And a column for each status and one for the list of resources (of Type Contact List)

Should look like this:

The formulas in the count cells would look like this with external sheet references back to all the project sheets:

=COUNTIFS({Project 1 Metrics}, $Resource@row + ">" + [Not Started]$1) + COUNTIFS({Project 2 Metrics}, $Resource@row + ">" + [Not Started]$1) + COUNTIFS({Project 3 Metrics}, $Resource@row + ">" + [Not Started]$1)

Now, when you add tasks to the projects and assign them, your counts will be dynamic.

I hope this makes sense and helps you. Again, one of many ways you can achieve what you want.

Cheers,

Ramzi

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!!!