Collect Values from a Group and See if it Contains a Certain Value
I have a sheet in which the person in charge of a project is responsible for providing weekly updates. As part of the update, they need to indicate the status of the project (ongoing, paused, stopped). I want to have a column that looks through the Status column for a given Project. If one of the rows contains "Stopped" or "Paused", I want the column to say "Not Active". Otherwise, it will show "Active" as shown in the highlighted column. This will make it easier for me to filter Active programs on my report and dashboard. How can I do this? I think I would end up using the COLLECT function as the inner function =COLLECT(Status:Status, Project:Project, Project@row). But I'm not sure what the outer function should be.
Best Answer
-
Seems like you are looking for the below:
=IF(OR(CONTAINS("Stopped", COLLECT(Status:Status, Project:Project, Project@row)), CONTAINS("Paused", COLLECT(Status:Status, Project:Project, Project@row))), "Not Active", "Active")
Answers
-
Hi @Camille
Try this as a column formula in the Activity Indicator column. If I'm understanding your needs to correctly, you want that column to be automated based on the Status column.
=IF(CONTAINS("Ongoing", Status@row), "Active", IF(CONTAINS("Stopped", Status@row), "Not Active", ""))
Let me know if this helps!
-
Hi @Sarah_H ,
Thanks for getting back to me so quickly. That's not quite what I was looking for. For example, for all of Project 1, if at least one of the cells in the Status Column contains "Paused" or "Stopped", then the Activity Indicator would show Not Active for all Project 1 rows (first 4 rows).
-
Seems like you are looking for the below:
=IF(OR(CONTAINS("Stopped", COLLECT(Status:Status, Project:Project, Project@row)), CONTAINS("Paused", COLLECT(Status:Status, Project:Project, Project@row))), "Not Active", "Active")
-
Hi @Leibel Shuchat ,
That worked! Thank you.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 454 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!