Collecting a list of names while referencing to other sheet

Sug.bhar

I have two sheets and I am trying to populate the names of all attendees in each meeting from the reference sheet into the destination sheet. I am trying to match the meeting names (for example" weekly meeting" or "leadership meeting" across two sheets, then identify the names that have been marked as "yes" for those meetings and collect the names from the name column and populate the names in the first sheet.

I am using the formula below, however this is only returning one name from the reference sheet. I want to get all the names of the people attending that meeting in the same column. How do I do that? Will JOIN or COLLECT work with this?

=(INDEX({Team Range 2}, MATCH([Meeting Name]@row, {Team Range 7})))

Reference Sheet

Destination sheet

Hollie205

Join Collect should work. Below is an example you will need to create your column references to your reference sheet

=Join(Collect({Column reference for name column},{column reference for weekly meeting column},"yes"),",")

Then do the same formula replacing your column reference for weekly meeting column to each of the other meeting column references.

Sug.bhar

Thank you @Hollie205. After I posted, I tried Join, Collect. And it worked exactly the same way you wrote above. I was trying to create a formula that I can expand for the entire column without having to replace column reference for each type of meeting.

Do you think something like this will work (setting up COLLECT statement with 2 criteria)

=JOIN(COLLECT({Team Range 7}, {Team Range 3}, =[Meeting Name]@row, {Team1}, ="Yes"), "-"). Its giving me an error saying "Incorrect Argument Set"

Hollie205

I can't think of any way to prevent having to do a cross sheet reference for each meeting column. If you do it on the same sheet then you could but cross sheet requires separate references.