Assigning Contact Based on data in another sheet
Here's the rough scenario.
I have 500 students who are divided up among 20+ teachers. When I choose a student in one column, I want to auto-assign their teacher in another column. I do this in Google Sheets with a formula, but wasn't sure how to go about it here.
Let me know if you need more clarification.
Thoughts?
Gillian
Answers
-
Hi @Gillian
I hope you're well and safe!
You could either use a Workflow combined with the Assign people action or have a Teacher Directory sheet and then use INDEX/MATCH to collect the information.
Would any of those options work/help?
I hope that helps!
Be safe, and have a fantastic week!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Index/Match is what I currently use so learning how that option could work here would be great.
Gillian
-
Since I'm so new, I'm struggling to learn how to set things up in Smartsheet which is different from Google Sheets. Maybe I'm overthinking it (likely), but normally I would have one tab on a sheet hold my reference data (students and their related info, such as teacher, grade, teacher email, etc). Then I can use that to populate another sheet by selecting the student name and then have the other info automatically filled in from the other tab.
In this situation, the primary column is a bit perplexing to me and I am uncertain what data I should place there.
Thanks for all the help. This is a great community.
Gillian
-
Hi @Gillian
I agree with @Andrée Starå that it sounds like an INDEX(MATCH formula is a good solution for you in this scenario!
Yes, set up one specific "reference" sheet with one column containing the Student ID or Name (something unique) and then the other columns housing the other data.
Then in your current sheet, you'll use the Student Name to find the matching row in the Reference sheet to bring in the required cell data.
An INDEX(MATCH works like this:
=INDEX({Column to return}, MATCH("Matching Value", {Column with value to match}, 0))
So in your case something like:
=INDEX({Teacher Reference Column}, MATCH([Student Name]@row, {Student Name Reference}, 0))
See: Formula combinations for cross sheet references
This Webinar has more information at about 17 minutes in: Essential Formulas for Smartsheet Users
If you have more columns to populate, you can then copy/paste this formula into your second column, delete out the first {cross sheet reference}, and replace it with a new reference to a new column in your Reference Sheet:
=INDEX({Second Reference Column}, MATCH([Student Name]@row, {Student Name Reference}, 0))
You may also want to review these two Help Articles:
In regards to the Primary Column, you can put anything in this Column that will be Text or a Number. I would personally use it to be a identifier for the row that helps sort or organize the sheet... for example a class name or the student ID. You could even put a column formula in that column (see: Work With the Primary Column: Overview and Best Practices and Use column formulas to apply calculations to all rows in a sheet)
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K 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