Chart the progress of several projects

Noah Fenley

My customer is looking for a chart to demonstrate weekly progress across several projects. All she wants to see is the project name, percentage and the weekly date. I am struggling with a clean way to provide the top level information from a project. I tried "copy a row" with a workflow but it copies all the subtask as well. She is looking for a chart like the attached image. She made the chart in Excel to demonstrate what she wants.

Paul Newcome

Are you wanting to show the current week and the past two weeks automatically updating to the current week, or a set range of dates, or...?

Noah Fenley

She wants to see several weeks leading to eventually show a year. We have 40+ projects in play at any time, so this would provide a quick glance at any projects stalling, starting or ending.

Paul Newcome

Would it be a rolling year up to today? How are you currently calculating/recording your percentages?

Noah Fenley

It's a new request but it will be a rolling year eventually. Percentages are being manually collected from the top level of the projects. I created a sheet and started copying the row weekly on Wednesdays. The issue is it copies all the rows instead of the top row.

Paul Newcome

Do you track start and end dates on each project for each task?

Noah Fenley

No, dates are floating. We track the start date, but use a random date for the finish date to allow the project to calculate the percentage. Unfortunately I work in a dynamic environment where the dates are not visible until something happens.

Paul Newcome

Can you provide an example of how exactly you are currently calculating your percentage complete?

Noah Fenley

I'm using a Project sheet where each task accumulates to a percentage of the total project.

Paul Newcome

Right. And how do you calculate the percentages for each task?

Noah Fenley

Depends on the project, but the percentage is a default field in the project settings.

Paul Newcome

Hmm...

What if you create a rollup sheet where the top row of each project is linked into a master/summary sheet? Then you can use the copy row automation from there to capture the static percentages each week.

Noah Fenley

I tired this but since the top row has sub-task, it copies the entire project. Most of the projects have a few hundred rows so week 1 resulted in a few thousand rows but we only needed the data from a few rows. The only reasonable solution so far is to use a workflow to send the projects as an attachment so we have a running record of the percentages and populate a sheet manually. Not a good solution when running this many projects.

Paul Newcome

Sorry. I wasn't clear in my suggestion.

You create a completely separate sheet and use cell linking to link the top row of each project onto it's own row within this Master sheet.

So this one sheet will be nothing but cell links and each row will be linked to the top row of each project.

Since this sheet only contains that top row from each project, the Copy Row Automation won't have any additional sub-task rows to copy.

Noah Fenley

Pretty sure I solved it. I used a sheet for "Project Collection" and cell linked for each active project. This gave me a list of projects with the current percentage. Then created another sheet "Project Percentage Collection". The Project Collection sheet has a workflow to copy the rows weekly to Project Percentage Collection. This creates a weekly log of the percentages for each project. From here I will have a modified date, percentage and project name. Creating the chart will be the next element.

Paul Newcome

Yes. That's exactly what I was referring to. Sorry for not being very clear. Coffee hasn't kicked in yet apparently. Haha.

As for building the chart, I personally would create yet another "Metrics" sheet where you can reformat the data from your "Percentage Collection" sheet into something a little more easily used.

The Metrics Sheet would have one Date type column and then an additional text/number column formatted to percentages for each project.

The Date column would have each of the dates used for copying the percentages into the "Percentage Collection" sheet. One entry for each date (I personally would start this in row 2).

Then in row 1 of each project column, I would enter the name of the project as it appears on the "Percentage Collection" sheet.

Then in row 2 of the first project column you can enter something along the lines of...

=INDEX(COLLECT({Percentage Collection Sheet Percentage Column}, {Percentage Collection Sheet Project Name Column}, [First Project Column]$1, {Percentage Collection Sheet Date Column}, $[Date Column]@row), 1)

This will look at the Project Name column on the Percentage Collection sheet and compare to the Project name in row 1. Then it will look in the Percentage Collection sheet's date column and compare to the date in the Metrics sheet date column. Once it finds a row where those two data points match (specific project on specific date), it will pull from the percentage column of that row.

Because of using cell references on the Metrics sheet ([First Project Column]$1 and $[Date Column]@row) and locking in either the row or column using the $, you can now dragfill this formula down and to the right to very quickly populate the rest of the Metrics table.

Now you have a table that you can use to build a chart in a dashboard that should look pretty close to what you have in your original post.