Automate Status Change Between 2 Sheets

I have two sheets, both with Status columns. Ideally, when the status in sheet B changes, I would like the status in sheet A to change. For example, if my tester (sheet B) changes the status to "Test Failed", I would like my Defect Critical status change to "To Be Reviewed" Can this be done through automation or do I need to work with formulas to make this happen?

Thank you in advance!

Find more posts tagged with

Sheets

Automations

Project Management

Accepted answers

Darren Mullen

@Christen Formulas will be needed for cross sheet changes, unless you wanted to get into developing something for updating this over the API.

All comments

Darren Mullen

@Christen Formulas will be needed for cross sheet changes, unless you wanted to get into developing something for updating this over the API.

Christen

Thank you for the verification!

Christen

@Darren Mullen Thank you again for your verification on my approach. I tried the following formula and rec'd a #NoMatch message.

=IF(INDEX({E2ETesterStatus}, MATCH([SCENARIO ID]@row , INDEX({E2ETesterStatus}, 0, 1), 0), 2) = "Failed", "To Be Reviewed", "")

Thoughts on how I could modify this formula to support the following scenario:

Using Scenario ID as the common key between the 2 sheet status. If the Status on sheet B changes to "Failed", change Defect Review Status, on Sheet A, to "To be Reviewed"

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.