Hello,
We run a weekly Sales report of any new Yellow or Red risk customers, that gets added into an Escalation Master Tracker in Smartsheet. Master Tracker may contain matching "Customer Code" entries more than once (past entries marked "Completed" or "Terminated") and new entry status = "Not Started", "In Progress", or "Watch" (open escalation). Before it gets added to the Master Tracker, we use an Analysis Sheet as a backend table to feed other data, as well as Conditional Formatting used to show any changes or highlight entries that need to be added to the Tracker.
What I am trying to do on Analysis Sheet 1, column "Open Escalation", is return a value of "YES/NO" when matching "Customer Code" in Master Tracker Reference Sheet 2 (CEM Range_Risk Analysis) "Status" column = "Not Started", In Progress" or in "Watch" (YES). So I can filter by any with NO and add those to the Master Tracker for that week.
The problem I am running into is if there are multiple entries already on the Master Tracker, some already marked "Completed" and the logic stops there. When there are more than 1 entries though I need to know if one of them are "In Progress" and my current formula is returning the first lookup Status when "Customer Code" is a match, which is usually "Completed".
So, Analysis Sheet 1 results as value, "NO" Open Escalation, see example below for SWY has 2 entries marked "Completed" but one marked "In Progress" so it should say "YES" Open Escalation because the Status for at least 1/3 is already "In Progress" (or "Not Started" or "Watch").
Original Formula:
=IF(VLOOKUP([Customer Code]@row, {CEM Range_Risk Analysis}, 4, 0) = "In Progress", "Yes", "No")
Not sure if I should be using IF CONTAINS formula with INDEX/MATCH or INDEX/COLLECT? Any help would be greatly appreciated!
Analysis Sheet 1
Master Tracker Sheet 2 {CEM Range_Risk Analysis}
