Return a Contact from another Sheet based on value
Chris Walker
✭✭✭✭✭
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)
Answers
@Chris Walker
Try an index match instead.
=INDEX({Assigned Staff (A)},MATCH([Project Column (B)]@row,{Project Column (A)},0))
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.).
Worked for me multiple times...