Using JOIN COLLECT or INDEX COLLECT

Judith Campf

I am using two Smartsheets and pulling in information from one sheet to another sheet through an INDEX formula - =INDEX({Live Lesson Reviews (Quality Assurance) Range TPD}, MATCH([Active Teachers]@row, {Live Lesson Reviews (Quality Assurance) Range TN}, 0))

Sheet 1: Live Lesson Reviews (Quality Assurance) contains two ranges - Training or PD Topic (TPD) and Teacher's Name (TN)

Sheet 2 (where the INDEX formula is located): matching the column called "Active Teachers"

The formula works, as you can see from the image above; however, if this teacher is listed in more than one row, it isn't pulling both sets of results. I want it to look for the teacher's name and combine the information on the other sheet under the PD or Training Topic column. Right now, it is only pulling the first one it finds.

Suggestions for joining or combining these results into the one cell?

Judy

Paul Newcome

You would need to use a JOIN/COLLECT to pull in the data from multiple cells based on the single name.

Judith Campf

Can you give me the formula that I need? I have tried using JOIN(COLLECT... but it doesn't work. I know I must be missing something that is required to get it to work.

Judy

Dale Murphy

@Judith Campf To build on the idea from @Paul Newcome try:

=JOIN(COLLECT(Topic:Topic, Name:Name, Teacher@row), " ") where Name == your range for teachers from the remote sheet, Topic == your PD info from the remote sheet, Teacher == content of your Active Teachers column.

dm

Judith Campf

Got it to work! My thanks and happy holidays!!

Judy