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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    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.

  • Gillian
    Gillian ✭✭

    Index/Match is what I currently use so learning how that option could work here would be great.

    Gillian

  • Gillian
    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