Cross-sheet formula based on multiple conditions
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!
Answers
-
=JOIN(COLLECT({Speaker Code}, {Session #}, [Session ID]@row, {Status}, "Confirmed"), ",")
I included only the relevant information in the screenshots.
-
Does using { } pull from the full column? So {Speaker Code} tells to look at all rows in that column?
-
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.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!