Nested Formula in Sheet Summary

Emily Swartz

I am looking to return a summary sheet for manager review.

I want a formula to run if the status is "In Progress" and it's been assigned to a specific team member, I want the value returned to be a list of the types of projects in progress. The sheet is set up with "Assigned to" as a contact list, "Status" to be a drop down list, and "Project Type" is a multi select drop down list.

Is this possible?

Thanks,

Emily

Andrée Starå

Hi Emily,

Not sure I follow!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Paul Newcome

From the sound of it, you may be able to use something along the lines of

=JOIN(DISTINCT(COLLECT([Project Type]:[Project Type], Status:Status, "In Progress", [Assigned To]:[Assigned To], CONTAINS("John Doe", @cell))), ", ")

Emily Swartz

Hi again,

Paul, thanks for the formula. It didn't return a value but I think I'm going in the right direction.

Here is a screen shot of the sheet:

I'm looking for a more concise return than what is in the "Project Types" summary specific to person and status.

Thanks again,

Emily

Paul Newcome

I don't see any names in the [Assigned To] columns.

Emily Swartz

They are further down the sheet as I didn't want to share the names. It's a contact list.

Emily

Paul Newcome

Ok. And what is the exact formula you are using?

Emily Swartz

Right now its =JOIN(COLLECT([Project Type]:[Project Type], (Status:Status, "In progress"), [Assigned To]:[Assigned To], "Name", @cell)), ", ") and it's not returning anything.

Paul Newcome

Try using the formula I provided and only replace the name.

EDIT: When I provided my original formula, I didn't realize that [Assigned To] was a contact type. Now that I know that, we need to make a slight adjustment.

=JOIN(DISTINCT(COLLECT([Project Type]:[Project Type], Status:Status, "In Progress", [Assigned To]:[Assigned To], FIND("John Doe", @cell) > 0)), ", ")

You should only have to replace the name in Bold with whatever name you want to search on.

Emily Swartz

It worked! Thanks!

Paul Newcome

Happy to help! 👍️

Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one might be found here.