Assigning Contact Based on data in another sheet

Gillian

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

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!

Gillian

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

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

Genevieve P.

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:

• Cross-sheet formulas
• Create a Cell or Column Reference in a Formula

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