INDEX from another sheet's multi-contact column

Will Jeffords

Hi, I have a "Project Master" sheet that lists all our projects and has a multi-contact column where I keep all the resources (contacts) that are working on each project. On a set of other sheets, I am trying to collect and index the set of projects to which a single resource is assigned (and I need that to be a metric sheet, not a report, from which I build reports/dashboards/etc). I can get the indexing to work when the projects only have a single resource (contact) in that column, but I cannot figure out how to get the index to work for projects where the contact is one of many. Am I missing something obvious?

L_123

You can give the collect formula a try.

https://help.smartsheet.com/function/collect

If you want more help I would need more details like what you are comparing to what and so on. A screenshot with any sensitive details blacked out would help.

Will Jeffords

Right, so currently I have;

=INDEX(COLLECT({Project Master Project Name}, {Project Master Resources}, ="Jake Lord"), [Column2]2)

I'm sure there is an issue because of the ="Jake Lord" part and that only being true when he is the only contact in the cell of that column, but I don't know how to build the formula to also include the Project Names when he is one of multiple contacts in the cell on those rows.

Does that clarify my issue more?

L_123

To add another range just stack them up in the collect. As many as you want.

=INDEX(COLLECT({Project Master Project Name}, {Project Master Resources}, ="Jake Lord",{Project Names},@cell = [Project Name]@row), [Column2]2)

Also i'm guessing [Column2]2 is an integer reference for the index? Typically with these types of equations you can just stick a 1 on the end like below.

=INDEX(COLLECT({Project Master Project Name}, {Project Master Resources}, ="Jake Lord",{Project Names},@cell = [Project Name]@row), 1)

* EDIT

On reread I think I understand what I was missing. Give this a try:

=INDEX(COLLECT({Project Master Project Name}, {Project Master Resources}, contains("Jake Lord",@cell)), [Column2]2)

Will Jeffords

Ok, thank you! that got me further along, but I'm finding that it only works if I create a non-contact type column that takes the contacts and puts them in a text format. Do you know of any way to avoid that step? Is there something about the Contact type (particularly when there are multiple contacts in cells) that prevents the indexing on a single contact therein?