Flag a case in one sheet if it is in a certain status on another sheet

Hey Y'all,

I need some formula help. Here's what I'm trying to accomplish:

If Case 1234 is in “Review” status on Sheet A, and someone enters Case 1234 on Sheet B, I want to flag Case 1234 on Sheet B so that no one takes action on it while it is still in a review process on Sheet A.

If Case 1234 is in any status other than “Review” status on Sheet A, I do not want to flag it on Sheet B.

Case 1234 could be entered on Sheet A multiple times and have different statuses for different entries.

Ideas?

Find more posts tagged with

Accepted answers

Paul Newcome

In that case, you would use an IF/COUNTIFS to count how many rows have that status for that case and say if that count is greater than zero, flag it.

=IF(COUNTIFS({Sheet A Case Column}, @cell = [Case Column]@row, {Sheet A Status Column}, @cell = "Review") > 0, 1)

All comments

Paul Newcome

Are you wanting to flag if there is even one instance of "Review", or are you wanting to evaluate the most recent status?

Claire Graves

I want to flag if there is even one instance of "Review."

Paul Newcome

In that case, you would use an IF/COUNTIFS to count how many rows have that status for that case and say if that count is greater than zero, flag it.

=IF(COUNTIFS({Sheet A Case Column}, @cell = [Case Column]@row, {Sheet A Status Column}, @cell = "Review") > 0, 1)

Claire Graves

Worked perfectly, @Paul Newcome! Thank you!

Paul Newcome

Happy to help. 👍️

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.