How to copy a row to the bottom of the same sheet but replace one cell contents with another

Options

Hello Community,

I have a sheet to track review dates, and one of the columns calculates the next review date based on some criteria. I would like to set up an automation to copy the row to the bottom of the same sheet, but replace the "review date" with the calculated "next review date" from the original row. Does anyone have any ideas for how I can do this? I'm assuming I may need to make a "helper" sheet with a formula for the new review date, but I'm having a hard time wrapping my head around how to move the "next review date" to the "review date" column, then delete the "next review date", then have some sort of trigger to copy it back to the original sheet. Any help would be appreciated!

Answers

  • Vince Darrigo
    Vince Darrigo ✭✭✭✭
    Options

    @mtp105 what would the trigger be that causes the row to move to the bottom? A date being reached in another column? A change in cell value somewhere? My idea would be in line with what you mentioned (helper sheet, or a boomerang sheet if you will)

    Seeing a screensnip of the columns you're working with (and maybe some fake data examples in them) would help a ton :)

  • mtp105
    mtp105 ✭✭
    Options

    Thanks Vince! I gave it a try with a helper or boomerang sheet (good description!) and got it to work! My trigger was when a row changes and "Next Review Date" cell changes - is populated using a calculation with inputs in other cells. So I did an automation workflow with this trigger, moved it to the helper sheet, used a formula for the new "Review Date" is equal to the "Next Review Date", then another automation to clear the cells that drive the calculation when a new row is added, and move it back to the original sheet. It worked! Sorry, I guess I posted my question too soon since it seemed a little outside my knowledge at the time, but a little trial and error helped me work it out. Thanks for your reply!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!