Automatic Move Row and External Sheet References

mattbartlett
mattbartlett ✭✭
edited 12/09/19 in Formulas and Functions

I am having issues with the Automatic move row function and external sheet references.

It doesnt seem that the automation will trigger if the formula needs to look up a value from a different sheet. Proofing is not turn on for this sheet. See screenshot for the formula

Automation: If cell= "a number", then move to another sheet.

Item 1: Date column from the received PO spreadsheet

Item 2: Look up value (PO number in this case) from the local sheet

Item 3: PO column in the Received PO sheet.

 

I do notice when I open the sheet, it takes a second or so for the lookup of references to be calculated, so maybe the status of the cell is not being stored when the sheet is closed?

 

 

 

SS Formula.jpg

Comments

  • Genevieve P.
    Genevieve P. Employee Admin

    You are correct: currently the Move Row automation requires the trigger to be based within the source sheet. A cell link, either from a cross-sheet formula or from cell-linking, cannot be the trigger for this type of automation. (Please submit a Product Enhancement Request when you have a moment!)

    You may want to create a helper check-box column that could be the trigger for the workflow, instead. Then, set up an easily recognizable conditional formatting rule based on your preferred column (for example, turn the row blue if this column contains a number). The next time you open the sheet you can quickly check all the boxes for the rows that should be moved, then save the sheet to trigger this workflow.

    Cheers!

    Genevieve

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi Matt,

    The third-party service, Zapier could also be an excellent option for this scenario. Is that an option for you?

    Hope that helps!

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    work-bold

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!