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

Claire Graves

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?

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)

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. 👍️