Returning a cell content if a name exists in a column

Esharmy
Esharmy ✭✭✭
edited 10/17/22 in Formulas and Functions

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:

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Esharmy
    Esharmy ✭✭✭

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!