Need help with pulling contacts from another sheet

Office of the Medical Director

Hi.

I have three different agencies that have a list of employee names and email addresses. When I do data entry into Smartsheets, I am trying to pull the list of the agencies email addresses into the form so I can send them an email. Here's the formula I have so far, but I'm getting an #incorrect arguement set.

=IF(Agency@row = "TFD", INDEX(COLLECT({TFD Contacts List - Email}, {TFD Contacts List - Name}, [Primary Apparatus Crew]@row, 1), IF(Agency@row = "NWFD", INDEX(COLLECT({NWFD Contacts List - Email}, {NWFD Contacts List - Name}, [Primary Apparatus Crew]@row, 1), IF(Agency@row = "GRFD", INDEX(COLLECT({GRFD Contacts List - Email}, {GRFD Contacts List - Name}, [Primary Apparatus Crew]@row, 1)))))

What am I doing wrong??

Here's a screen shot of my data entry form:

Here's a screen shot of my contacts list:

* I removed the email address for these employees so it's not published

Thoughts?

Ramzi

I believe your issue may be that your not closing your parentheses on the COLLECT function properly. You appear to be missing a ")" in the following spots:

Here's the fixed formula: (you may need to ensure the parens are balanced at the end)

=IF(Agency@row = "TFD", INDEX(COLLECT({TFD Contacts List - Email}, {TFD Contacts List - Name}, [Primary Apparatus Crew]@row), 1), IF(Agency@row = "NWFD", INDEX(COLLECT({NWFD Contacts List - Email}, {NWFD Contacts List - Name}, [Primary Apparatus Crew]@row), 1), IF(Agency@row = "GRFD", INDEX(COLLECT({GRFD Contacts List - Email}, {GRFD Contacts List - Name}, [Primary Apparatus Crew]@row), 1))

Office of the Medical Director

@Ramzi

After working through this more, I decided to have all of the employees listed in 1 additional form. So this is the formula I was able to get working.

=INDEX(COLLECT({Field Crew Contacts - Email}, {Field Crew Contacts - Agency}, @cell = Agency@row, {Field Crew Contacts - Name}, @cell = [Primary Apparatus Crew]@row), 1)

The problem is that it only works to look up the employee name if there is only 1 name in the field. If there are 2 contacts in the Primary Apparatus Crew cell, the formula won't work.