Can an error message generated by a formula be used to trigger a workflow?

I've seen suggestions in other forum posts that this does not work, but nothing conclusive.

I have an index/match formula to link data from sheet 1 to sheet 2. If the indexed row in sheet 1 is later deleted (the project is cancelled, for instance) there is no longer a match and I get a #NO MATCH error in sheet 2.

I want to use the change from a date to #NO MATCH as the trigger to run a 'move row' automation to my Cancelled Projects sheet.

so, A) is it fundamentally ok to trigger based on a column formula, and B) is an error like #NO MATCH a viable trigger? And if both are Yes, why else might the automation not trigger??

By the way, everything works as intended when I have the Move automation set up with a manually entered value such as X in a non-formula cell... but nothing happens when I change it to the column containing the formula.

Find more posts tagged with

Formulas

Automations

Accepted answers

All comments

Scott Peters

Hi @Josh W - I do this today with a checkbox column to check if my formula column is an error. Something like this:

Then I set up an automation that runs when the "Is Error" checkbox turns to checked. Sometimes that's a little too aggressive: If someone sees an error, the sheet saves, but they quickly correct their input error, the automation might fire unnecessarily. An hourly workflow might be better. Hope this is useful!

Josh W

That looks like a really good workaround, I'll try it. Thanks!

I'll assume you are not able to get the automation to trigger directly from a #DIVIDE BY ZERO value in 'Your Formula Column'??

Josh W

Follow Up/ Result:

I'm not able to use the check box to directly trigger the Move automation, BUT I now have this set up to trigger every day at midnight & use the checked box as the condition required to move the row. It would be nice to have it somewhat instant, but this sheet is not viewed by the general public anyway and an overnight cleanout of unmatched rows is fine. Thanks!

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.