Help Required

Hi,

I think I might need help with using MATCH but not 100% sure if its the right way to go

I have a sheet with a column named Job Number and another column named Status

I am looking to search for any matches in the job number column and if there is a match AND the status column in this matched row is closed I want to set my initial column status also to be closed.

Hope that makes sense !!

Any help appreciated

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Are you able to provide a screenshot for context?

Dan Crimmins

Hi Paul,

so you can see the Job number column and the status column.

As per the initial query I'd like it if (in the red row) the Job number matches any other rows and any of those rows have a closed status then the status of the initial (red) row is updated to closed

Paul Newcome

You are going to need to insert a hidden helper column with the following column formula:

=IF(COUNTIFS(Status:Status, @cell = "Closed", [Job Number]:[Job Number], @cell = [Job Number]@row)> 0, "Close Row")

Then you would set up a Change Cell automation to trigger when the hidden helper column changes to "Close Row" and the output would be Closed in the Status Column.

Dan Crimmins

Thanks Paul,

Formula works fine but I cant seem to get the workflow to change the cell, it remains the same regardless of is the cell contains "Close Row" or not.

I even added a new column to test it out but its not changing the value - Do I have a schoolboy error here ??

Paul Newcome

Are you saving after making the update that would lead to the workflow being triggered? It could also take up to 15 minutes for the workflow to trigger after the sheet has been saved. You may also need to refresh your browser after saving.

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.