Using join/collect to return task description when task is complete

Alex Hackford

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.

Paul Newcome

Try this:

=JOIN(COLLECT({Project Plan - EOL Asset Enterprise Range 1}, {Project Plan - EOL Asset Enterprise Range 4}, @cell = 1), ", ")

Alex Hackford

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

Alex Hackford

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.

Paul Newcome

@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.