Using JOIN COLLECT or INDEX COLLECT
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
Answers
-
You would need to use a JOIN/COLLECT to pull in the data from multiple cells based on the single name.
-
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
-
@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
-
Got it to work! My thanks and happy holidays!!
Judy
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!