Formulas lost when Move/Copy row from one sheet to another?

hanseric

We want to move rows (preferably using Automation) from a source sheet which contains formulas in the rows, without losing the formulas once the row arrives in destination sheet (the sheets are identical in terms of columns).

The source sheet is the first step on a broader flow which begins in that sheet.

Once the status of the row reaches a certain point in that sheet, it's time to move it to another sheet for different work to be done there.

However, when the row arrives at the destination sheet, the formulas have been stripped away.

Is there a way around this while still being able to move the row from one sheet to another?

OR...

Could we have the row somehow pick up a new formula in the destination sheet (e.g. inheriting it from the row above it in its new location)?

L_123

The way to automate this is to do exactly what you would do if you were adding a row via form instead of by the workflow. You need to add extra columns in the destination sheet that contain your formulas, and post the formulas in the rows above. Currently there is not a way to do this by editing the workflow.

Andrée Starå

Hi Hanseric,

Unfortunately, it's not possible at the moment to keep the formulas or have them autofill, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment.

As a possible workaround, you could maybe do it with the help the third-party service, Zapier or the Smartsheet API.

Is that an option?

I hope that helps!

Have a fantastic day!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD​​​​

Andrée Starå

@L@123 have you tested that so it takes the pattern from the rows above? I tested it a while ago and then it didn't work.

L_123

I tested it last week when I had an idea for an end of shift sheet and it worked, I can try building another to see if it still works.

L_123

Tested and works with a move to workflow

Andrée Starå

@L@123 Ok. Thanks! If it does, it opens up a new world of possibilities. 🤩

Andrée Starå

@L@123 Thanks! Maybe I tested with the copy workflow. Back to the lab! 😉

L_123

Yeah there are a lot of things that can be built with this. I hesitate to start developing with it though because as soon as they add the functionality of maintaining formulas in the copy and move workflows I'll have a bunch of sheets with extra columns hanging out though.

It seems to work with the copy as well. I just edited my test sheets from move to copy. Maybe they changed the functionality after you tested.

Andrée Starå

@hanseric It seems like it should work. Just make sure to add at least two rows with the same formula pattern

Andrée Starå

@L@123 Yes, but it depends on the timeline for that to be added.

L_123

True True.

My favorite part of this is actually the ability to remove the formulas.I built an extremely complicated end of shift setup using this that uses very long formulas in the main sheet, and because I can change those formulas to static values I can now maintain years worth of data without locking up the program.

Andrée Starå

Yes, I agree, and it has already opened up so many powerful workflows.

Cody Stevenson

Does anyone have any update on this thread. It seems that there are conflicting behaviors here. When I use automation to move a row, the formula of the source is lost and the formula of preceding rows in the destination are not updated for the new row. This is causing issues in my workflow/s.

Paul Newcome

@Cody Stevenson

The formulas from the source sheet will be lost when a row is moved. The Move Row feature only pulls static data. That is expected behavior there.

As for why the formulas in the destination sheet are not updated for the new row...

DO you mean that the formulas are looking at a range and the range does not shift when new rows are added, or do you mean that new rows don't autofill with the formula when they are added?