Using join/collect to return task description when task is complete
Hi,
I am trying to create a formula that returns a collection of tasks descriptions when the task is marked as 100% complete. The overall goal is to show, per project phase, what tasks have been completed to date.
Here's what I have currently: =JOIN(COLLECT({Project Plan - EOL Asset Enterprise Range 1}, {Project Plan - EOL Asset Enterprise Range 4}, 1){Project Plan - EOL Asset Enterprise Range 5},",")
Project Plan - EOL Asset Enterprise Range 1 = project task names w/in the phase
Project Plan - EOL Asset Enterprise Range 4 = Task completion
Project Plan - EOL Asset Enterprise Range 5 = project task names w/in the phase
Am I on the right track here? I'm getting an Unparsable response.
Answers
-
Try this:
=JOIN(COLLECT({Project Plan - EOL Asset Enterprise Range 1}, {Project Plan - EOL Asset Enterprise Range 4}, @cell = 1), ", ")
-
Unfortunately, I'm getting an Incorrect Argument Set on the formula you shared above.
I added a column to my project plan to indicate phase so I would want to add that as a condition as well. Something like...
=JOIN(COLLECT({Project Plan - EOL Asset Enterprise Range 1}, {Project Plan - EOL Asset Enterprise Range 4}, @cell = 1, {Project Plan - EOL Asset Enterprise Range 6}, 1), ", ")
Where range 6 is the Phase # column
-
I think I got it. The formula that seems to be working is as follows...
=JOIN(COLLECT({Project Plan - EOL Asset Enterprise Range 1}, {Project Plan - EOL Asset Enterprise Range 4}, @cell = 1, {Project Plan - EOL Asset Enterprise Range 6}, @cell = 1), ", ")
I just added @cell= to the last criteria and updated the range reference to include the entire column for range 4.
-
@Alex Hackford Glad you were able to get it sorted.
The Incorrect Argument Set error would have most likely been coming from the ranges not being the same. If one is referencing an entire column, then they all should. Otherwise you will get that error.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives