Dashboard Metrics - Pulling in Week

r0030

Hi everyone - I'm working backwards here but I have a Project Plan that I'd like to derive metrics from in regards to Client Name, Health, Status, and Week.

What I'm stuck on is how to capture which week in the Project Plan we're currently in. Is there any sort of automation that I'm not thinking of? I want to show these metrics on a dashboard so that leadership can easily see that we're at week 3/5(for example) of the project plan. Thanks in advance!

Paul Newcome

Is it going to be date based or based on % Complete? What sheet level data determines which week you are in?

r0030

https://community.smartsheet.com/discussion/comment/335446#Comment_335446

The dates are specified on the parent row (i.e. Week 0 - QBR Coordination & Internal Meetings) but there is also predecessors built in to drive towards the specified start and end date columns. So I imagine it would be built off of the start and end date columns?

Paul Newcome

Assuming you are working on a separate metrics sheet, your formula to pull in would be something along the lines of...

=INDEX({Source Sheet Task Name Column}, MATCH(MAX(COLLECT({Source Sheet Start Date Column}, {Source Sheet Start Date Column}, @cell<= TODAY())), {Source Sheet Start Date Column}, 0))

r0030

https://community.smartsheet.com/discussion/comment/335452#Comment_335452

Thanks Paul! This did allow me to pull in the task (schedule business review meeting with client & distribute invitation to all attendees), but I'd really like the Week column on this sheet to display a numeric value.

The problem is that the week is not shown on a task by task basis, but only at the parent level. Example below from the Project Plan:

Would it work if I created a helper column to specify week in the Project Plan and make it hidden, and then pull in something similar to the formula you provided earlier?

Paul Newcome

The helper column with the week number should do the trick.

r0030

https://community.smartsheet.com/discussion/comment/335483#Comment_335483

Thanks for your help, Paul!

Paul Newcome

Happy to help. 👍️

r0030

Hey @Paul Newcome - circling back to this :) Is there a way to pull in the health for each week as well? Currently I have a Health column set up with the RYG symbols, but I'd like to roll that up to show the overall health week over week.