Index match? trying to consolidate data

Tanya B

I have a started a sheet where a supervisor can select courses for employees. (Supervisors are allowed to select 2 courses and they run for 2 days)

For the trainer, my thought was to create a sheet for each course and do a lookup to the sheet to pull in the Students name, supervisor and the start/end date of the course. That way they could have a calendar view for each course.

Make sense? other thoughts? I am on smartsheet.gov

Lisa LS Kennedy

Personally, I prefer Index(Collect. It's faster than a Match to stack multiple criteria. INDEX(COLLECT("The range you want to collect", "the criteria range", "specific criteria"), 1)

or in your case

INDEX(COLLECT(Student:Student, Instructor:Instructor, Instructor@row), 1)

Whenever you want to bring in a new column of data, just change that first range to next column you want.

Lisa Kennedy

Senior Consultant | Smartsheet Development

Prime Consulting Group

Email: info@primeconsulting.com

Tanya B

@Lisa LS Kennedy Just getting around to try this but not very successfully :(

what I need is to look up A (either check box or 1, assuming I need 1) - if checked or 1 return the data from B to E.

Brian Wilson DC

@Tanya B Another solution might be to use automations. Essentially, duplicate your source sheet for the total number of classes you have and rename them each by the class name. Then when someone assigns a person to a class using the check boxes you could have it move that data (Copy Row) to the appropriate class sheet. Just hide the unneeded columns on the destination class sheet (essentially the check box columns) and your instructor would get a running list of students, supervisors and dates. You could also then setup and automation on the destination sheet to let them know when a new student was added to their class.

Just a different approach!