Return a Contact from another Sheet based on value

Chris Walker · 01/18/21

Hello,

I have a source sheet (Sheet A) that maintains a list of projects and assignments.

I need a second request sheet (Sheet B) to intake requests. On this sheet, the project name will be included. I would like to use a formula to output the name of the assigned staff member for that project (found in sheet A). (*the sheets are more robust than this, but for the sake of what I am trying to accomplish I have simplified*)

I've used an Index / Collect formula, but it seems to return the value in Sheet B as simple text, and does not provide it as a Smartsheet Contact. Both columns in Sheet A and B are of the Contact List type.

Is there another approach that can help retain the Contact List format when I calculate the value in Sheet B?

Sheet A:

Project Column (A)
Assigned Staff (A)

Sheet B:

Project Column (B)
Assigned Staff (B)

Current formula: =INDEX(COLLECT({Assigned Staff (A)}, {Project Column (A)}, [Project Column (B)]@row), 1)

Leibel S · 01/18/21

@Chris Walker

Try an index match instead.

=INDEX({Assigned Staff (A)},MATCH([Project Column (B)]@row,{Project Column (A)},0))

Chris Walker · 01/18/21

Thanks for the quick reply, Leibel!

This approach also returns a value, but it appears the outcome is the same as I had before - returning the name as text, and does not have the typical functionality. I've tried testing by adding an alert workflow to notify {Assigned Staff (B)} when a change in the row; and an alert/email is not sending.

I'm wondering if it is not possible to do a lookup on a Contacts List Type column and retain the functionality (i.e., user info/email, etc.).

Leibel S · 01/18/21

Worked for me multiple times...

Ryan_AltosLabs · 05/09/23

Hi Leibel,

Thank you for your help on this. I was able to have the formula return a single contact. I'm looking to have this same function but return multiple contacts. Do you by chance have a formula for that? I was previously using a Join/Collect function but to Chris's problem that only returns simple text, which is not sending out notification. I'm looking to return multiple contacts and use those contacts for alerts/notification.

Leibel S · 05/09/23

@Ryan_AltosLabs

There is no way to do that currently.

you can only return a contact via index, however if that contact field is a MULTI contact then that would come in as well.

Return a Contact from another Sheet based on value

Answers

Help Article Resources

Categories