calculating status based on status of multiple columns
I am trying to calculate the status of a main column ("status") based on the status of multiple columns (OP2-OP8).
The idea is to automatically change the status (on the left) from "Open" to "Complete" based on the individual status of the columns on the right (Open/Closed).
Background: we try to monitor the issue status of each line based on the progress of the issue per each OP. e.g. if the issue is open in at LEAST one OP the status stays open. ONLY if all four are CLOSED that entitles the main status to change to Complete.
The complexity is, that a certain issue is not necessarily applicable to all OPS (see Affected_OPs column).
Any ideas how to build a function around that?
Help Article Resources
Check out the Formula Handbook template!