calculating status based on status of multiple columns
Hi,
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?
Many thanks
Oren
Best Answer
-
I think there might be several logical approaches. The way I would probably do it is to use an IF Formula that counts all Opens in in the cells before it and flags it when there aren't any.
Try:
=IF(Countif(OP1@row:OP8@row., "Open") = 0, "Closed",. "Open")
Answers
-
I think there might be several logical approaches. The way I would probably do it is to use an IF Formula that counts all Opens in in the cells before it and flags it when there aren't any.
Try:
=IF(Countif(OP1@row:OP8@row., "Open") = 0, "Closed",. "Open")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!