Nested Formula in Sheet Summary
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
Answers
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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))), ", ")
-
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
-
I don't see any names in the [Assigned To] columns.
-
They are further down the sheet as I didn't want to share the names. It's a contact list.
Emily
-
Ok. And what is the exact formula you are using?
-
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.
-
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.
-
It worked! Thanks!
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!