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?
Best Answer
-
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)
Answers
-
Are you wanting to flag if there is even one instance of "Review", or are you wanting to evaluate the most recent status?
-
I want to flag if there is even one instance of "Review."
-
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)
-
Worked perfectly, @Paul Newcome! Thank you!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!