Returning a cell content if a name exists in a column
Hi all...another newbie question.
I'm trying to create a report that shows, by person, the project that they own and the projects that they are a "stakeholder" in.
I have a report titled "Projects" that has project title column "Project Name", a column where one name exists "owner", and another with multiple names "Stakeholders"
The report should look this listing out each person showing what projects they are involved in...
Person1 Project Name Owner (Y) Stakeholder (N)
Any help appreciated
Answers
-
Hi @Esharmy
If you're looking to create a Report type of Smartsheet item, you can create Filters in your Report to show you specific people assigned in different columns:
See: Create Filter Criteria to Control Data in Report Builder
However based on your description it sounds more like you need a Sheet with formulas in it to create a list of each person's projects. The way I would do this is to set up a sheet with 3 Columns:
- A Contact Column with each person listed
- The Primary Column with a formula bringing back all projects that are Owned per person
- A Text/Number Column with a formula bringing back all projects that each person is a Stakeholder in
The formula in both cases would be a JOIN(COLLECT formula (see: Formula combinations for cross sheet references), like so:
=JOIN(COLLECT({Project Name}, {Owner Column}, Person@row), " / ")
You can swap out the / symbol for something else to separate the project names per person.
However since you have multi-select for your Stakeholder column, you'll want to use the HAS Function in your second formula:
=JOIN(COLLECT({Project Name}, {Stakeholder Column}, HAS(@cell, Person@row)), " / ")
If you would prefer to separate out the Project Names as separate selections, you could make your two formula columns Multi Select columns and separate the values with a CHAR(10) line break instead of " / " like so:
=JOIN(COLLECT({Project Name}, {Stakeholder Column}, HAS(@cell, Person@row)), CHAR(10))
Let me know if this is what you were looking to do!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thank you so much...will give this a shot
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!