Weekly Comparison

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?

Answers

  • Andrée Starå
    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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å
    Andrée Starå ✭✭✭✭✭✭

    You can copy the rows automatically with a workflow.

    You don't need to do it manually.

    Would that work?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

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

  • Hi Andrée,

    Any thoughts on how to create the automated workflow?

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Jona Gjylameti I missed this!

    Do you still need help or have you solved it?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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å
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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
    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å
    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?

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • R Smith
    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!