Returns value with matching data (cross sheets)

firestorm · 07/15/21

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.

Bassam Khalil · 07/15/21

Hi @firestorm

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

firestorm · 07/15/21

Hello @Bassam Khalil

any examples?

Bassam Khalil · 07/15/21

@firestorm

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

https://community.smartsheet.com/discussion/74460/join-collect-with-if-statement

firestorm · 07/15/21

Hello @Bassam Khalil

The link you sent me is not working for me.

Andrée Starå · 07/15/21

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!

Bassam Khalil · 07/15/21

@firestorm

please click on View Post

Genevieve P. · 07/15/21

https://community.smartsheet.com/discussion/comment/298024#Comment_298024

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

firestorm · 07/16/21

Hello @Bassam Khalil

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

Andrée Starå · 07/16/21

@Genevieve P

Happy to help!

Genevieve P. · 07/16/21

https://community.smartsheet.com/discussion/comment/298170#Comment_298170

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

firestorm · 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.

Genevieve P. · 07/16/21

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

firestorm · 07/19/21

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.

firestorm · 07/19/21

@Paul Newcome

Any help?

Andrew McCreath · 07/19/21

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

Returns value with matching data (cross sheets)

Answers

Categories