Weekly progress report - project related

If I'm understanding you correctly, on each Sunday of the week you want to gather static information across all your projects/rows to identify how many tasks are in each status (irregardless of the date of the tasks). You want this number to stay static for the week that you gathered, but then the next week identify the next numbers, so that you can track progress. Is that correct?

If so, the way I would do this is to have two helper sheets. One would be to create the calculations each week, and then you would copy this data to a second helper sheet which is the source for your chart. This way the copied data is static and won't update as the formulas update each week with new numbers

First Helper Sheet: Formulas

The first Helper Sheet would have 3 Status Columns to gather the numbers.

Each column would have a COUNTIF formula looking at your source sheet, counting how many tasks are in each status. (Note: you could build this as 3 helper columns in your current sheet if you didn't want to create extra sheets - let me know if you'd prefer to go this route and I'll explain further).

Screen Shot 2021-05-28 at 11.48.56 AM.png

Then you would have a Workflow set up that Every Sunday the row is COPIED to the second helper sheet.

Screen Shot 2021-05-28 at 11.49.29 AM.png

Second Helper Sheet: Date Workflow

Then in your Second Sheet, set a Record a Date workflow in a new column that will record the Sunday Date every time a new row is created, or copied over from this other workflow.

Screen Shot 2021-05-28 at 11.52.03 AM.png

(My Date Column is named "Sunday")

In my tester sheet I triggered this on a Friday, so the date is a Friday, but in your workflow it would be a Sunday:

Screen Shot 2021-05-28 at 11.51.48 AM.png

Then as this changes, you should see an update each week as the source sheet continues to update with new status counts:

Screen Shot 2021-05-28 at 11.55.03 AM.png

You can use this as the source of your Chart if you want to select your range:

Screen Shot 2021-05-28 at 11.57.12 AM.png

Or you could create a Report from this second helper sheet to pull in non-blank rows and have the Report as your source. This means that the chart will automatically update without you needing to re-select a new range as your month goes on.

Is this what you were looking to do? Please let me know if I've misunderstood what your "2" represented!

Cheers,

Genevieve

Hi @Genevieve P

Hope you are doing well

I tried your way but nothing is reflecting on the 2nd Helper sheet

Hi @Genevieve P

Does this keep on updating below rows every week or you manually added the value for 16 may and 23 May

The second sheet will auto-populate each week, with the row information and the Sunday Date.

However in order to do that, you need to set up two workflows.

The First is a Copy Row workflow created in the sheet with your formulas to COPY the data once a week.
The Second is a Record a Date workflow in this second helper sheet (where the rows are copied to) to record the Sunday Date in the Date column.

Process:

Formula in Sheet 1 calculates numbers
Copy Row workflow (in Sheet 1) runs once a week to copy the data from Sheet 1 to Sheet 2
Record a Date workflow (in Sheet 2) runs when a new row is added to auto-populate that date.
Chart Widget is based off of Sheet 2's data.

Does that make sense? Let me know where you're getting stuck and I'd be happy to provide more screen captures explaining one of the points above!

Cheers,

Genevieve

Hi Genevieve,

Hope you are doing well!

One question I have here is does the update just count the weekly progress or we can have something that when it run on every Sunday it should give us the total count progress ...

let me know if you would like to view the screen capture .

Thanks

Bunny

The totals in the First Helper Sheet with the Formulas determines the data that you are counting. You can build this out however you would like, so yes, it's possible to create a total count progress.

The process I described above is so that each week, as the total count changes, you have a historical record of what the formula said last week, since this number will change over time.

What is the formula you are currently using?

Hi @Genevieve P

I am using the formula =COUNTIF({Project Plan}, "Complete")

I am looking for every sunday when it run it should give us the total count not just weekly count

Thanks