Using a Cross-sheet Formula Result for Automation/Report

Steve Reed

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.

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.

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.