Can I copy rows based on a formula updating?

Hi All -

I'm working on new process, and have everything working except one small (seemingly) problem. I have two sheets that talk to each other, and they work fine. In Sheet 1, I have a status field (drop down), and in Sheet 2 there is corresponding Status field. When Sheet 1's Status field gets changed, Sheet 2's Status field is updated via a VLOOKUP. All of that works completely fine.

What I am trying to do though, and is not working, is use an Automated Trigger to Copy a Row from Sheet 2 to Sheet 3 based on the Status in Sheet 2 changing to a certain value.

So, an example would be - when Sheet 2's Status field = "Complete", Copy the Row to Sheet 3. It's not working, and the only thing I can think is it's not firing because the Status in Sheet 2 is being updated via a formula (VLOOKUP) and the Automated Workflow doesn't recognize that.

Any thoughts / help would be great appreciated. I'm pretty stumped on alternative options.

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Jeff Reisman

Automations that change sheets won't get fired by changes from cross-sheet formulas. This is in place to prevent possible endless loops.

What will sometimes work is changing a helper cell based on another cell changing from a cross-sheet formula, but that's not guaranteed.

What will always work are time-based automations where the value produced by the cross-sheet formula is a condition of the automation.

Mike Rini

@Jeff Reisman Jeff - that's awesome info, and what I was thinking was going on. So is a viable solution (using the time-based automation) something like - setting the Automation to look at the Status every hour, day, etc.? I'm just trying to see if it's the "when triggered" part that's the issue.

Jeff Reisman

https://community.smartsheet.com/discussion/comment/343652#Comment_343652

Yes, exactly - The trigger becomes the scheduled time, with the status value being the condition telling the running automation to move the row.

Mike Rini

Ok, I'm going to give it a try and see if it works. Hoping that delaying it to an hour helps. Maybe a stupid question, but one I think could screw this up - in the very first part where you set the Trigger - should I just leave it as "When rows are changed" and "Any field Changes", then set the Condition to the Status value?

Mike Rini

So I tried a couple of different things:

Changing the timing from "when triggered" to "hourly" The problem is, the Automation forces it to "when triggered", and it doesn't work.
I added a helper cell which basically updates when the status changes (via a simple if-then-else). It updates fine, but isn't triggering it.

What's weird is that I'll get sporadic rows copying over occasionally. For an example, last night I was testing this out, and did a bunch of scenario testing. None of them were showing up in the sheet. Randomly this morning, a bunch of them just showed up copied over - all with the proper modified time stamp.

It just doesn't seem consistent, so I'm still trying to find a consistent way to do it.

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, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.