insert multiple contacts based on cell value

MMeyers

I have a sheet I use for daily assignments for our field workers. I want to be able to enter the lead for the day and have it populated the crew members contact for that lead.

This sheet populates the crew members by name and email.

From there it is brought into this sheet. I can enter the lead for the day and it will spit out the information from the first sheet.

However, I want the Crew Copy column to auto populate to the contact that is already in my contacts. Is there a way to do this?

Paul Newcome

Do you mean pulling it from the first sheet to the second? If so, an INDEX/MATCH should work for you.

=INDEX({Email Column}, MATCH(Lead@row, {Lead Column}, 0))

kurtwz

Hey MMeyers, OK you have a 2-tier hierarchy in one sheet with a 1-to-many supervisor-to-subordinate structure and you want to use that structure to auto-populate the subordinates whenever the supervisor is populated, right? If so, yes, that's possible through numerous approaches. However, only the workflow automation populates contacts. See below:

Our hierarchy made up of Darth Vader and Riff of the Jets from Westside Story:

Our sheet that uses the hierarchy sheet:

Please note that the Crew: Vlookup and Crew: Index, Match columns correctly pulled in the values for their Supervisor/Manager. However, they did so with the values being pulled in as strings. This isn't necessarily a problem if someone is going to look at this sheet and discern that it's him/her on that list. However, if this sheet will be used for downstream automation such as to automatically notify someone of something they have to do, that automation won't be (directly) possible with the …Vlookup or …Index, Match columns. I say it won't be directly possible because someone determined and clever could possibly find a way to enable this automation, but it would require additional dev.

Best solution I could find is to create and maintain workflows to assign the crew: