Using a Cross-sheet Formula Result for Automation/Report

I am facing a very challenging issue that I need some ideas on. I have a sheet that serves as a master bill of materials for a process. This sheet contains many rows that derive multiple values from a lookup sheet (achieved via cross-sheet index/match formulas). Two of these cross-sheet results are checkboxes that determine which items from the master sheet go to one of two subsequent Setup sheets after approval. Here are the conditions that need to be satisfied:

Status=Approved
Setup 1 or Setup 2=Checked (this is the cross-sheet formula result)

So, I have tried to accomplish this using 1) a Copy Row automation and 2) a report. Neither is working for me. I found a note in Help about Copy Row not being able to reference cross-sheet formulas as a trigger. It seems that reports do not recognize the cross-sheet derived checkbox as a valid Report Filter either.

Does anyone have any brilliant ideas on how to solve this obstacle. This is a major issue in this process which I did not expect to be a problem, but not it is. I appreciate anyone's input on my situation.

Find more posts tagged with

Accepted answers

Steve Reed

For anyone viewing this question, below are some findings that I have gotten today since my earlier posting:

The statement above (and on Smartsheet Help) that you cannot use a cross-sheet formula for a Copy Row automation is not entirely true. What we (Smartsheet Support and I) realized on an early afternoon call is that you can't use a cross-sheet formula resulting in a checkbox for this automation. However, once we changed the lookup result to be a text value (Yes/No), my automation worked! Until...
As indicated above, I originally had two cross-sheet formula checkboxes to direct the copied row to two different destination sheets. Once we changed them to text values as described above, the first one worked perfectly...HOWEVER, the second one did not work AT ALL. Another call to Smartsheet Support confirmed that all of my configuration was correct, but no luck on the second automation.
At the end of our call, I came up with an theory and possible solution. My theory was that somewhere deep in the code, Smartsheet was allowing one cross-sheet formula to operate, but there was a limitation built in (purposely or not) preventing the second. So my alternative solution was to put the intelligence for the routing into a single column on my lookup sheet. Since I already knew that it worked with a single column, this new configuration would trigger my automations from that one column. Believe it or not it works now.

All comments

Steve Reed

For anyone viewing this question, below are some findings that I have gotten today since my earlier posting:

The statement above (and on Smartsheet Help) that you cannot use a cross-sheet formula for a Copy Row automation is not entirely true. What we (Smartsheet Support and I) realized on an early afternoon call is that you can't use a cross-sheet formula resulting in a checkbox for this automation. However, once we changed the lookup result to be a text value (Yes/No), my automation worked! Until...
As indicated above, I originally had two cross-sheet formula checkboxes to direct the copied row to two different destination sheets. Once we changed them to text values as described above, the first one worked perfectly...HOWEVER, the second one did not work AT ALL. Another call to Smartsheet Support confirmed that all of my configuration was correct, but no luck on the second automation.
At the end of our call, I came up with an theory and possible solution. My theory was that somewhere deep in the code, Smartsheet was allowing one cross-sheet formula to operate, but there was a limitation built in (purposely or not) preventing the second. So my alternative solution was to put the intelligence for the routing into a single column on my lookup sheet. Since I already knew that it worked with a single column, this new configuration would trigger my automations from that one column. Believe it or not it works now.

Kyle Chipman

@Steve Reed From years in the future, thank you for this detailed summary. Though it didn't work in my use case I appreciate the rigor of follow-up and documentation.

What did work for me (and might work for others) is the following:

Create a recurring "Record a Date" automation, using "When a date is reached" as the trigger, to record a date in a specific column at a specific time. Then, build the "Move a Row" or "Copy a Row" automation, where the "Record a Date" column is the trigger, and set the conditions to test against the columns with the cross-sheet references.

Safe Smartsheet travels for anyone else reading this in the future :)

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.