Triggering Workflows without Accessing the Sheet

bdelehanty

I have the following workflow:

Sheet 1 (an individual project) statuses are updated

Sheet 2 (a summary of that individual project) has calculated fields based off of counting types of statuses in Sheet 1

When Sheet 2 values change the row is copied over to Sheet 3 (a portfolio sheet)

Since Sheet 3 is getting a new row each time a value in Sheet 2 changes, I end up with multiple records for each project; I get around this by calculating a "most recent record" field. When that is set to "N", the record is moved to Sheet 4 (a trash sheet since there is no "delete" automation)

Where i'm having trouble is that none of this seems to happen unless i actively open every sheet in that order. For instance, if i change a value in Sheet 1 and then go to Sheet 4, i don't see updates. If I click through Sheets 1-4 it works. I'd obviously like this to all happen without my interaction so users can update the individual projects and managers can view the portfolio level sheets.

jmyzk_cloudsmart_jp

Hi @bdelehanty

According to a Smartsheet help article, cells which contain cross-sheet-formula won't trigger automation;

 "To prevent infinite approval loops, cells which contain cross-sheet formulas or cell links won't trigger an automation that automatically changes the sheet (Move Row, Copy Row, Lock Row, Unlock Row, or Approval Request). To work around this, consider using time-based automation or recurrence workflows.".

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

So, your Sheet2 should not trigger the copy rows automation since the trigger is a cell change by cross-sheet-formulas. (I do not know why "If I click through Sheets 1-4 it works.")

As in the following example, I would use a report to get portfolio status counts.

Dashboard

https://app.smartsheet.com/b/publish?EQBCT=df02c34fc7bc4ed7833750fb300da0fe

Report

https://app.smartsheet.com/b/publish?EQBCT=86c861c564a94be09ea6bc06cd8fd8cb

bdelehanty

Ok that’s what I was seeing too. So how I read that is if I’m triggering off a cross sheet formula, but what if I added a modified date field? If I’m not mistaken that’d change when those formula fields change (based on change in sheet 1) and I could trigger off of that?

there’s just got to be some work around here!

jmyzk_cloudsmart_jp

Hi @bdelehanty

I tested adding a "modified" column.

Still, the modified data change does not trigger an automation

If the initial triggers are cross-sheet-formula or cell-link, the resulting changes do not trigger automation.

My conclusion:

cross-sheet-formula or cell-link change > "modified" etc. change > not trriger automation.  😔