Move Rows to Another Sheet...that contain formulas

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.

Find more posts tagged with

Sheets

Finance

Formulas

Services

Project Management

Enhancement Request

Comments

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.

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?