Can I copy rows based on a formula updating?

Mike Rini

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.

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:

1. Changing the timing from "when triggered" to "hourly" The problem is, the Automation forces it to "when triggered", and it doesn't work.
2. 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.