Returning a cell content if a name exists in a column

Esharmy

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

Genevieve P.

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:

1. A Contact Column with each person listed
2. The Primary Column with a formula bringing back all projects that are Owned per person
3. 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

Esharmy

Thank you so much...will give this a shot