Move Rows to Another Sheet...that contain formulas

Rooke Jackson

Hello! 

Has anybody had experience trying to automatically move a row containing formulas to a new sheet? I am trying to pre-populate cells with formulas that would apply on a different sheet so that they will be ready to go once moved over during a workflow.

Paul Newcome

Have you tried putting those formulas in the first two rows of the target sheet THEN moving the rows from the source sheet? 

 

I do know that the moved rows will go to the first unused row in the target sheet, but I am not sure (haven't tested that far yet) whether or not it will trigger the auto-fill functions already built into SS similar to if it was a form entry.

Rooke Jackson

Hi Paul!

No, not yet. Doesn't "move row" automation create a new row in the target sheet? So it seems the formulas added to the target sheet would be recognized as "used" rows. 

Paul Newcome

Yes. It does create a new row. Yes. It will recognize the rows with formulas as "used". But that's where Smartsheet's auto-fill feature kicks in. If the two rows above the new row contain the same formula that formula will autofill into the newly created row whether that be from a manually created row (tested and confirmed), a form entry (tested and confirmed), a manually moved row (tested and confirmed) and a row moved through automation (tested and confirmed).

 

So all you need to do is take the first two rows of your destination sheet and use them as "Formatting" rows. Set up whatever formatting (font, fill, etc) and whatever formulas you want to auto-fill into the new rows, and it should all format into each new row as they are created.

tbittick

This was very helpful and seems to have pretty much solved my issue;

I've set it up so the manager can enter tasks into a form which then populates a Master Tracking sheet and the sheet of the assignee.

Following the help provided here I made sure both sheets had two rows populated with the desired formulas.

When I manually entered a new task, the auto formatting work and the formulas carried over to the new row.

When I do the same thing with the automated process one of the formulas carries over and one does not. I've tested several times and it the same result happened.

I'll say up front Formulas are not my strong suit.

The formula that does NOT carry over is here: =IF(ISBLANK([Due Date]@row), "", IF([Due Date]@row < TODAY(), 1))

It triggers a flag when a task is past due

The formula that DOES carry over is here: =IF([% Complete]@row < 0.5, "Red", IF([% Complete]@row < 0.7, "Yellow", IF([% Complete]@row < 0.9, "Blue", "Green")))er

It triggers the color of the health status "ball" between four colors based on how far out the due date is and what percentage has been completed. This formula seems to transfer about 90% or better.

Happy to get any help available. Many thanks.

Brianna Kempfer

I am following this thread. I have a master sheet that copies cells over to a less complex sheet. Within that sheet, i have index/match to obtain the necessary information (that way if it is updated here, it will also update on the other sheet). I was going to cell link, but there are too many rows and items that would need to do this and be maintained.

I have greater than 2 rows above where the new row moves, but the index/match is not applying to the cells that i need to auto-fill. My other cells that have formulas that are not index/match are copying over fine.

Any ideas on if it is possible to auto-fill these numbers, if there are numbers already in there but may change?

Will Sargent

Try the new "column formula" feature in the destination sheet. That will fix it. You'll have to set it up in the destination, it won't copy across.