Returns value with matching data (cross sheets)

firestormfirestorm
edited 07/15/21 in Using Smartsheet
07/15/21 Edited 07/15/21
Answered - Pending Review

Hello, I have a sheet to receive project requests with reference, descriptions, and team members in 2 columns, the main person-in-charge, and other team members (multiple contacts). There is also a project status column.

I have a separate sheet for respective project members to update on the status, e.g. Jose. I would like to populate the "Reference" and "Description" in his sheet if his name appears in either of the 2 columns, and if the project is not "closed" in the status column.

How do I get all the projects if Jose appears in multiple rows?

Appreciate your help.


Previous1

Answers

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    Hi @firestorm 

    Hope you are fine, you can use Join With Collect Function.

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello @Bassam Khalil

    any examples?

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @firestorm

    the link i add to you in my replay contain an example, and also check the following post


    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hello @Bassam Khalil

    The link you sent me is not working for me.


  • Andrée StaråAndrée Starå ✭✭✭✭✭

    Hi @firestorm

    I hope you're well and safe!

    You'd have to click the link shown under the Leaving part (at the bottom).

    @Genevieve P Could this be updated to not show up for Smartsheet Community and similar URLs?

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Bassam KhalilBassam Khalil ✭✭✭✭✭

    @firestorm

    please click on View Post

    PMP Certified

    [email protected]

    www.mobilproject.it

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Hey @Andrée Starå

    That's a good callout! I'll see if we can set the Help Center to be a trusted URL.

    Thanks,

    Genevieve

  • Hello @Bassam Khalil

    I don't understand the post, can you help using my case?

  • Andrée StaråAndrée Starå ✭✭✭✭✭

    @Genevieve P

    Happy to help!

    SMARTSHEET PARTNER & CONSULTANT / EXPERT

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E: [email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

  • Hi @firestorm

    In this instance, you'll actually want to add two separate JOIN(COLLECT formulas together. One will look in the "in charge" column for your Contact, the other will look in the "secondary" column.

    I presume that the sheet where you're placing this formula has the contact you're searching for defined in a column. In my example, I'll call this column "Contact Name".

    The way it works is that you define the column you want to return data from in your COLLECT function, then you list the column that contains criteria (your Contact column) and identify what it's looking for (the contact specified in this current sheet).

    In my example I've then used the symbol / to separate different values.


    Here's an example of how you'd build this to find all the "references" for one contact:

    =JOIN(COLLECT({Reference Column}, {In Charge Column}, HAS(@cell, [Contact Name]@row), " / ") + " / " + JOIN(COLLECT({Reference Column}, {Secondary Column}, HAS(@cell, [Contact Name]@row), " / ")


    Here's how it would look to find all your "descriptions" for one contact:

    =JOIN(COLLECT({Description Column}, {In Charge Column}, HAS(@cell, [Contact Name]@row), " / ") + " / " + JOIN(COLLECT({Description Column}, {Secondary Column}, HAS(@cell, [Contact Name]@row), " / ")

    Does this make sense? Let us know if you need further help!

    Cheers,

    Genevieve

  • firestormfirestorm
    edited 07/16/21

    Hello @Genevieve P

    Thanks for your explanation. I forgot to mention that each member has his/her own destination sheet, where I want the project references and descriptions (linked to their names) to appear. Hence, in my example, Jose has his own sheet to update.

    So the information to pull from the project request sheet are the project reference and descriptions, to be entered side by side in 2 columns. The other info to be updated by Jose will be in the other columns.


    Hope this is clear.

  • Hi @firestorm

    Thank you for clarifying! In this instance you'll actually want to create a Report instead of a second sheet with formulas. The formula suggested above can only gather data into one cell, versus splitting out the relevant rows into each row.

    In the Report, select your source sheet as the sheet. Then set the Filter to be if either the In Charge column OR the Secondary column contains that contact. You can also add a condition that the project "is not closed". Here is information on how to create a Report, and here's a webinar on Reports.

    Let me know if you still have questions after reviewing the articles above!

    Cheers,

    Genevieve

  • Hello @Genevieve P

    I can't use report as I will need to pull data from these individual sheets for metrics, since formula cannot reference to reports.

  • @Paul Newcome

    Any help?

  • Hi @firestorm - you can use reports to pull from multiple sheets. The process starts with you clicking New Report, Row Report. Once you have given it a name you progress to the Sheets selection, here find and select the sheets that contain the data.

    Click next and then select the columns required and carry on as above.

    Hope this helps,

    Andrew

Sign In or Register to comment.