Week to Week % Complete Progress?

rmc0030

I have a % complete column in my project plan. Is there anyway to show progress on initiatives from week to week, for example, if we were at 30% last week and the report comes out and we’re now at 40% it shows in that box +10% or something??

I was thinking it will help show progress and if there is none, it could help show where to push on the project?

Nick Korna

Hi @rmc0030,

You could certainly do this with some automation. At a given point (e.g. last thing Friday or first thing Monday), have the rows copied to another sheet. You can then add a column which would do a INDEX COLLECT along the lines of:

=INDEX(COLLECT({Project Archived Progress %}, {Project}, Project@row), COUNTIF({Project}, Project@row))

This will find the last progress of the project on the row. You can then either subtract this from the current progress to find the change or skip entirely by altering the formula slightly to show the change:

=[Current Progress]@row - INDEX(COLLECT({Project progress archive Progress %}, {Project}, Project@row), COUNTIF({Project}, Project@row))

Hope this helps, but if you have any problems/questions, just let us know.

rmc0030

Thank you,@Nick Korna . This is really helpful. I just set up my archive sheet and am experimenting with the formula but keep on getting the "unparseable" message.

Could you clarify where I should be referencing the archive sheet vs the columns in the sheet i'm working in with the formula? Thank you so much for your help!

Nick Korna

You need to set up the cross sheet references to get rid of the errors as you most likely don't have any currently set up. These are the parts of the formula in the { } brackets, which you can rename as desired - I just have them that way so you can hopefully work out reference refers to which column. Some articles on how to do this:

https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

https://help.smartsheet.com/articles/2482644-create-cross-sheet-references