Weekly Comparison

Jona Gjylameti

Hello guys,

I am trying to add a column that will provide me a %complete for previous week. I have this week's percentage and a PM is updating that weekly however I was wondering if there is a way to have another column that would be updated with previous week's percentage without the manual labor (think of 300+ rows). Is there a way to have a formula or something automated that would make my column static and update only as we progress throughout the days or am I asking for something currently impossible?

Andrée Starå

Hi Jona,

The third-party service, Zapier, is an excellent option for this scenario.

Another option could be to copy the rows to another sheet.

Would any of those options work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jona Gjylameti

Hi Andrée,

Copying the rows would need to be done manually on a weekly basis and that's a lot of overhead. I'll take a look at this Zapier option but not sure if our organization would be open to that.

Andrée Starå

You can copy the rows automatically with a workflow.

You don't need to do it manually.

Would that work?

Jona Gjylameti

yes! How would that work? With a formula? And will it update weekly?

Jona Gjylameti

Hi Andrée,

Any thoughts on how to create the automated workflow?

Andrée Starå

@Jona Gjylameti I missed this!

Do you still need help or have you solved it?

Jona Gjylameti

I did a linking cell by cell from current week and then I'm copying that information over to "previous week" after reviewed with team. I wish we could get the zapier tool but that's not the goal at this time.

Thanks for checking

Andrée Starå

Happy to help!

We can set it up without Zapier, I think.

We'd copy the rows automatically to another sheet and have the previous week's numbers added.

We'd then use a VLOOKUP or INDEX/MATCH formula to collect the previous week's percentage.

Make sense? Would that work?

Jona Gjylameti

The problem is that we have about 10 people updating 10 different sheets. The current week % is what they usually update. The previous week is being managed at a high level sheet that is linked to the current week % from these other 10 sheets and the previous week is being copied over each week after reviewed with them. We don't want to add more work for the project managers on updating the previous week and we want to minimize error as well. I would have wished we had a more "automated way" to do this but for now it's working.

Angie Hatfield

Hi @Andrée Starå

My CEO just asked for this, to show the difference from the previous week.

I'm looking at the automated workflow and I see the copy row to another sheet. What about copying a column?

All of my projects have a roll up of the completed tasks and all projects are rolled up on one sheet.

Example:

Project % Complete

task % Complete

task % Complete

Project % Complete

task % Complete

task % Complete

I really need to copy a column rather than a row. Ideally I would copy the % Complete column at noon on Tuesday to another column, Previous % Complete, which would be immediately after I send out an update. It would then be ready for next the next update I send out the following Tuesday.

Right now I am considering adding a column and pasting the values in so it is ready the following week but an automated work flow would of course be nice. (Nothing like a CEO to disrupt a non-touch process 😀).

Thanks!

Andrée Starå

@Angie Hatfield

It's not possible to copy a column.

I think we could make it work with a VLOOKUP or INDEX/MATCH and copy/move row workflow or by using a the 3rd party service, Zapier.

What do you think? Would one of those options work?

R Smith

FYI: I have about the same need as Angie listed above. I need to compare a column from this week against what it was last week.

I was about ask how to do that with Index Match since I can't copy a column, then it occurred to me I can just copy the whole sheet (auto copy every row that is not blank once a week to a "last week" (and just prior running that automation clear off all the rows from "last week") and then do Index Match against the "last week" sheet. That is a lot of overhead for just one column (it is a big sheet) but I think it will work.

I was excited when they added the option to update cell values with automation but then disappointed when I realized I could not use a formula as simple as 'make this cell = that cell'. I hope they add that option, it would be a lot less hassle than all the copying of whole sheets I am going to end up doing now.

Thanks!