'Request an update' Automation: 'Condition' where column contains a cross-sheet reference.

Will the automation “Request an update” fail if the 'Condition' column contains a cross-sheet formula that pulls data from another sheet?

For example, there are two Smartsheets:

Sheet ONE contains Project ID, MFG Rep and Customer Price. This is the source sheet for Project ID and MFG Rep.

Sheet TWO contains Project ID, MFG Rep, Vendor Cost and Ship Date.

Instead of typing or selecting duplicate info on two sheets, we link Sheet TWO ‘Project ID’ to Sheet ONE ‘Project ID’, and we use cross-sheet formulas (index/match) to pull in MFG Rep name from Sheet ONE. In Sheet TWO, I have setup an 'Request an update' automation workflow based on Sheet TWO MFG Rep (column contains the cross-sheet formula) and if the column contains the Reps first name, email them a Request for Update Form so the Rep can enter Vendor Cost on Sheet TWO.

I noticed that the sheet TWO MFG Reps are not getting the email. Is it because the ‘condition where’ column cannot contain a formula?

Thank you for your help!

Answers

  • MCorbin
    MCorbin Overachievers Alumni

    It shouldn't fail - I have automation based on formula columns. HOWEVER: You have to have accessed the sheet (either by opening it or by having it referenced in a dashboard that was opened) in order for the field references/formulas to update.

    Also check to make sure that your column type for MFG Reps is a Contact List and that those 2 reps have emails associated with their name in the list.

  • EmilyH
    EmilyH ✭✭✭✭✭

    Thanks for the reply.

    On Sheet TWO (destination sheet), the MFG Rep (formula pulls source data from Sheet ONE) is formatted as Contact List.

    But on Sheet ONE (source sheet), the MFG Rep column is formatted as a Pull-Down (not Contact).

    The Automation on Sheet TWO is setup as: If the row’s “Project ID” is added (any value) and the MFG Rep ‘contains’ the person’s first name, the automation alert is ‘send to specific people’ email address added to automation. The MFG Rep column on Sheet TWO contains both Contacts (embedded with the person’s email address) and Text (no email address referenced). Due to business decisions, I cannot change Sheet ONEs column format. But I don’t understand why I cannot add a condition to look for the person’s first name, then send to a specific persons email address. It does not seem to work.

    Note: the formula in MFG Rep index/matches to 3 Smartsheets and it is based on 7 different pull-down options. Is the complexity of the formula inhibiting the automation to work?

    Thank you for your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!