Automation back and forth

Two identical sheets. They both contain a column EMP#, which is pulled from a third sheet based on NAME. When there is a value for EMP#, stay on sheet1, when it is blank, move to Sheet2 (and vice versa).

I think because EMP# has a formula, no rows are moved. I tried a helper column with

=IF(NOT(ISBLANK([EMP#]@row )), 1, 0)

then based automation on that column when rows change to either 1 or 0, but each automation moves all rows. What am I doing wrong?

Find more posts tagged with

Accepted answers

jmyzk_cloudsmart_jp

Hi @MWJ

First, the move rows and copy rows automation has this limitation to prevent an endless loop.

To avoid creating endless loops, Smartsheet doesn't initiate the Copy Row action when the trigger cell includes cross-sheet formulas or is linked to other cells. To work around this, use time-based automation or recurrence workflows.

https://help.smartsheet.com/articles/2479626-automatically-copy-rows-between-sheets

So, I set up the workflow's trigger to be time-based. (See the middle image.)

The image below is before the trigger.

I used Run now, on the 2nd Sheet, and here is what the 1st Sheet gets.

Then, at noon, rows 2 and 4 were moved to the 2nd Sheet.

It may be a good idea to change the trigger time on the 2nd Sheet.

By these setups, every day at noon, EMP# blank rows on the 1st sheet move to the 2nd Sheet.

Then, at 1:00 PM, from the 2nd Sheet, all the EMP# blank rows, including the one moved from the 1st Sheet, move to the 1st Sheet.

Alternatively, as the updated 2nd Sheet's image shows, if you add the "Created By" column, the rows moved or created by the Smartsheet automation have 'automation@smartsheet.com', we can change the automation to exclude them.

But my question is, why do you want to move the rows back and forth in the first place?

One possible scenario would be that each Sheet has a different reference, so on another sheet, the name may get the EMP#.

P/S

I added a name, Yoshi, in the 5th row of the 2nd Sheet to see what happens at 1:00 PM.

The result

As expected, only the 5th row was moved, but the Created By is not automation@smartsheet.com.

So, the 3rd condition in the above workflow may not be so reliable.

All comments

jmyzk_cloudsmart_jp

Hi @MWJ

First, the move rows and copy rows automation has this limitation to prevent an endless loop.

To avoid creating endless loops, Smartsheet doesn't initiate the Copy Row action when the trigger cell includes cross-sheet formulas or is linked to other cells. To work around this, use time-based automation or recurrence workflows.

https://help.smartsheet.com/articles/2479626-automatically-copy-rows-between-sheets

So, I set up the workflow's trigger to be time-based. (See the middle image.)

The image below is before the trigger.

I used Run now, on the 2nd Sheet, and here is what the 1st Sheet gets.

Then, at noon, rows 2 and 4 were moved to the 2nd Sheet.

It may be a good idea to change the trigger time on the 2nd Sheet.

By these setups, every day at noon, EMP# blank rows on the 1st sheet move to the 2nd Sheet.

Then, at 1:00 PM, from the 2nd Sheet, all the EMP# blank rows, including the one moved from the 1st Sheet, move to the 1st Sheet.

But my question is, why do you want to move the rows back and forth in the first place?

One possible scenario would be that each Sheet has a different reference, so on another sheet, the name may get the EMP#.

P/S

I added a name, Yoshi, in the 5th row of the 2nd Sheet to see what happens at 1:00 PM.

The result

As expected, only the 5th row was moved, but the Created By is not automation@smartsheet.com.

So, the 3rd condition in the above workflow may not be so reliable.

MWJ

@jmyzk_cloudsmart_jp

To answer your question, employees leave and return to the active roster fairly regular. The Third sheet maintains this (by EMP#). So when an archive employee returns (and gets their EMP# reassigned), their data goes back to the first sheet. That's why I thought I could get around the loop thing.

But, the time-based workflow is the solution to my issue. Thank you!

jmyzk_cloudsmart_jp

Thank you, @MWJ . Intriguing with a real-world example — the roster cycling between active and archive status makes total sense now.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.