Update Status Column Automatically, with Special Conditions /// Automation

I have two sheets: Sheet 1 collects form information about a new program proposal. There is a column labeled "Status" that is updated via the form to "Program Proposal Submitted". Once a reviewer OKs the proposal by checking a box, the row is copied to Sheet 2.

Sheet 2 tracks the development of the program. Once the row is copied, I want "Status" to change to "Stage 1". There are two decision points in the process: Stage 1 and Stage 2. In the end, I want the "Status" column to update with when these two fields—-[Stage 1 (Decision)]@row and [Stage 2 (Decision)]@row are updated.

If [Stage 1 (Decision)]@row = "Approved for Next Stage" change Status to "Stage 2" OR, if it = "Not Approved for Next Stage", change Status to "Archived Idea" OR if it = "Revisions Required", keep Status at "Stage 1".

Same would be true for [Stage 2 (Decision)]@row.

I tried setting up a Helper1 column (H-Status@row) to equal the value of [Stage 2 (Decision)]@row, or its empty, the value of Stage 1 (Decision)]@row. Then in Helper2 column ([H-Status (Update)]@row), a checkbox is checked if the value in Helper1 is different than value in Status.

Then I created an Automation that, if the Helper2 box is checked, it has two conditions:

(1) Stage 1 (Decision)]@row is NOT BLANK and [Stage 2 (Decision)]@row is BLANK. If so, if the cell value in Stage 1 (Decision)]@row is "Approved for Next Stage", change value of Status@row to "Stage 2" OR if value in Stage 1 (Decision)]@row is "Not Approved for Next Stage", change value of Status@row to "Archived Idea" OR if value in Stage 1 (Decision)]@row is "Revisions Required", change value of Status@row to "Stage 1".

NOTE: There are a few other options in the Stage 1 (Decision)]@row that should change Status@row to "Stage 1", but for simplicity I just included one condition in this question.

Logically, this seems good to me. However, because the Stage 1 (Decision)]@row and Stage 2 (Decision)]@row are updated via a cell reference to cells in Sheet 1 (the one collecting form submission information), the Helper1 and Helper2 are happening but not TRIGGERING the automation. And, since I am changing cell values via the automation, I can't set the "Run Workflow" of the automation to Hourly. So something needs to trigger the automation to run.

Any suggestions about how to either (1) cause some kind of trigger to run the automation, even if I am already on the sheet or if I load the sheet later than when Sheet 1 is updated via a new form submission or (2) use a different approach to update Status@row automatically as a result of a change in Stage 1 (Decision)]@row or Stage 2 (Decision)]@row?

Find more posts tagged with

Accepted answers

All comments

L J

One quick thing to look at…
In your three Conditions in the Automation (Stage 1 Decision is one of….), Smartsheet will ONLY look at the next condition over when the previous condition is not true.

For example, if "Stage 1 Decision" is one of "Approved for Next Stage", it will NOT look at any of the other conditions, even if they themselves are true (it's one or the other, not cumulative).

While they look fine, you noted that you simplified the conditions in your example, so it might be worth checking if this scenario applies? You could even drop an action (e.g. send yourself an email) just above those conditions to verify that the trigger is happening.

aschneiderheinze1025

Thanks for the insights. I am more concerned on triggering the automation. The changes to the columns included in the workflow are updated when new form submissions occur in another sheet. They include cross-sheet reference formulas. I am not able to use a time-based trigger (e.g., Hourly). And when updates occur automatically in any of the columns with the cross-sheet reference formulas, even if I refresh the sheet, the Status doesn't update, per the workflow.

Should the automation still run automatically, i.e., will an update resulting from a new form submission on the other sheet thus updating the columns in this sheet because they contain cross-sheet reference formulas (even if it requires refreshing the sheet)? I'll double-check the conditions as you suggested.

L J

"To prevent infinite loops, Smartsheet doesn’t trigger actions that change the sheet automatically by inbound cell-links or cross-sheet formulas. This includes formulas that refer to another cell with an inbound cell link or cross sheet formula.

For example, if the Status cell on the row is populated by a formula pointing to a cell with a cell link from another sheet, changes to that cell value through the cell link won’t trigger a workflow that locks the row when Status changes to Complete."

Smartsheet's recommendation to work around this is to consider using time-based automations (which you can't, as you've said) or recurrence workflows. REF: https://help.smartsheet.com/articles/2479236-trigger-blocks-when-your-workflow-is-executed

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