Cross-sheet formula based on multiple conditions

JBrenier

Trying to create a cross-sheet formula based on multiple conditions in one sheet to join values from multiple cells into a single cell in a different sheet.

In Sheet #1, if the Status Column = Confirmed will use Join(Collect) formula to pull all Speaker Code values (separated by a comma) into field into Sheet #2 Speaker Code field. Match based on session # which could have multiple rows in sheet #1 but only listed once on Sheet #2.

I know how to do the Join(Collect) formula just not sure how to ask to join based on session # cross sheets.

Example: In sheett #1,3 rows are associated with Session #1, 2 have Status = Confirmed. Want JulieBrenier,JaneDoe to populate in a field in Sheet #2 Speaker Code field in the Session #1 row.

Sheet #1 -

Sheet #2

Hope this makes sense!

Carson Penticuff

=JOIN(COLLECT({Speaker Code}, {Session #}, [Session ID]@row, {Status}, "Confirmed"), ",")

I included only the relevant information in the screenshots.

JBrenier

Does using { } pull from the full column? So {Speaker Code} tells to look at all rows in that column?

Carson Penticuff

Yes, as long as you have setup the reference to the other sheet for the column. {Status} as a cross sheet reference is essentially the same as saying [Status]:[Status] inside the same sheet.