Cross Referencing Data Between Sheets

I need to find a function that I can use to simplify some enrollment data reporting. Rather than entering it in manually...every day...it should auto-update based on what enrollment data is entered in another sheet.

Looking at the pictures provided...in the ENRL (Experimental) column, I would like it to return the enrollment number from a different sheet if the CRN in column 1 matches the CRN in the enrollment sheet.

Is this possible? Thanks!

Find more posts tagged with

Accepted answers

Jeff Reisman

INDEX/MATCH can handle this for you.

As your begin your INDEX formula, Smartsheet will pop up a helper screen. Click Reference Another sheet, and select your enrollment sheet, then select the Enrolled column. You can choose to name this range if so desired. Same thing for the CRN column. This process creates the references for your formula.

=INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))

In English, Get me the value in the Enrolled column from the Enrollment sheet where the Course/CRN number on this row = the CRN number on the Enrollment sheet.

Since it appears you don't have a valid entry for Course/CRN on every row, you'll want to wrap the formula in IFERROR to prevent #NO MATCH errors.

=IFERROR(INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))"")

All comments

Jeff Reisman

INDEX/MATCH can handle this for you.

=INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))

In English, Get me the value in the Enrolled column from the Enrollment sheet where the Course/CRN number on this row = the CRN number on the Enrollment sheet.

Since it appears you don't have a valid entry for Course/CRN on every row, you'll want to wrap the formula in IFERROR to prevent #NO MATCH errors.

=IFERROR(INDEX({Enrollment Sheet Range 1}, MATCH([Course/CRN]@row, {Enrollment Sheet Range 2}, 0))"")

College Employee

https://community.smartsheet.com/discussion/comment/317326#Comment_317326

@Jeff Reisman -

That worked PERFECTLY!! Thank you so much for the help!!

Very Respectfully,

-C

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.