Formula to match two column inputs within one row in two separate Smartsheet's.
Roster Smartsheet:
Course Email Eval Completed
Class1 Joe@email.com =formula here
Class1 Jane@email.com =formula here
Class2 Joe@email.com =formula here
Evaluation Smartsheet:
Course Email
Class1 Joe@email.com
Class1 Jane@email.com
Class2 Joe@email.com
Formula that doesn't work:
=IF(AND(RosterSS[Course]=EvaluationSS[Course],RosterSS[Email]=EvaluationSS[Email]),"yes","no")
QUESTION:
How can I write the formula to look up the course column and email column in two different Smartsheets and match them to answer Yes or No on whether each email (student) has completed a course eval for the class they attended.
Best Answers
-
You can use COUNTIFS to check if there is a row in the evaluation sheet with the class and email matching.
See below formula, change the cross sheet reference (Bolded) to match yours...
=IF(COUNTIFS({Evaluation Sheet - Course Column},@cell= [Course]@row,{Evaluation Sheet - Email Column},@cell=[Email]@row)>0,"Yes","No")
-
@Leibel Shuchat - Thank you for the correct formula! This works great.😃
Answers
-
You can use COUNTIFS to check if there is a row in the evaluation sheet with the class and email matching.
See below formula, change the cross sheet reference (Bolded) to match yours...
=IF(COUNTIFS({Evaluation Sheet - Course Column},@cell= [Course]@row,{Evaluation Sheet - Email Column},@cell=[Email]@row)>0,"Yes","No")
-
@Leibel Shuchat - Thank you for the correct formula! This works great.😃
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!