Can I pull data from different columns and deposit them each into a specific cell?

Options

Hello!

I'm creating a report for students who check in for class from a form. The sign-in sheet is the same for all of the classes, so it would have several different dates and classes on it for the whole school year. It has a single-select dropdown column for them to select the class, and a "Full Name" column that has a column with their names. The "Full Name" column is a formula that combines their first and last names to detract from any misspellings. The "Date of Class" column is in calendar format.

Is it possible to have a formula that pulls from the "Class" and "Date of Class" columns, while referencing the "Full Name" column (which is on the master sheet and the sign-in sheet as a formula combining the names), and deposit that information in the "Courses" column on the master sheet?


Sign-in Sheet:


Master Sheet:


Tags:

Best Answer

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Answer ✓
    Options

    If I'm understanding correctly - I would suggest using a helper column in the sign in page - which will join the Class & date attended.

    Something like this:

    ="Course: " + Class@row + " Date Attended: " + [Date of Class]@row

    And of course you can modify what it says as you please. - That will put together the Class Name & Date Attended.

    Then on the Master Sheet, I'm a little unclear:

    Do you then want to show all of the classes attended by each student in 1 cell?

    That's doable - but I think it'll look a little funny.

    Is that what you want to try to do?

Answers

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Options

    Hey @fennerb !

    I have a couple of clarifying questions real quick:

    #1 - Do you have multiple sign in sheets you will be pushing to the Master Sheet - or is it all coming from 1 - (despite having multiple classes they all use the same form?)?

    #2 - You want to have the Master Sheet lookup the the Full Name of the Student & pull the "Class" into "Courses" and "Date of Class" into "Course Date"? (for the purposes

    #3 - also is the Master Sheet a "Sheet" or a "Report"?

  • fennerb
    fennerb ✭✭✭✭
    edited 04/07/22
    Options

    Hey @Jon Mark H!

    Thanks for responding!

    #1: Just the one sign-in sheet. We're keeping track of it that way by the date entered and a time stamped response to make sure that they're not trying to backtrack and say they were there on a different day. That, and it would be too much for the person in charge to keep track of several sheets. That, and I'm using a QR code for each sheet for them to check in.

    #2: We're trying to see if we can pull from the "Date of Class" and the "Class" on the sign-in sheet and deposit it onto the master sheet into the "Courses" column while referencing the students' names. I'm trying to see if we can have the class and date in one cell. Because the current way doesn't make sense that it's put together. I did a quick-and-dirty dump of all the class dates in the "Course Date" column and it just shows the dates without what date the class was linked to. I took a screenshot of the data below. It's not organized at all, and I'm trying to basically combine the two left columns. So, basically, I want the "Courses" column to show that "Intro to DPD" was attended on 2/10, "(M.C.O.L.E.S)" was attended on 2/15, and so on. We're not even sure if that's a possibility. If not, we'll have to find another way to make it make sense.

    #3: In a sense, the "Master Sheet" is pretty much a report. I wanted to utilize this as a basis for a dashboard to show class attendance, demographics, GPA, and so forth.

  • Jon Mark H
    Jon Mark H ✭✭✭✭✭
    Answer ✓
    Options

    If I'm understanding correctly - I would suggest using a helper column in the sign in page - which will join the Class & date attended.

    Something like this:

    ="Course: " + Class@row + " Date Attended: " + [Date of Class]@row

    And of course you can modify what it says as you please. - That will put together the Class Name & Date Attended.

    Then on the Master Sheet, I'm a little unclear:

    Do you then want to show all of the classes attended by each student in 1 cell?

    That's doable - but I think it'll look a little funny.

    Is that what you want to try to do?

  • fennerb
    fennerb ✭✭✭✭
    Options

    Yes! So, for example, Aaron attended [Class]es and combine them with these dates that he attended, just for him. And so on, for each student.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!