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

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:

Find more posts tagged with

Formulas

Accepted answers

Jon Mark H

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?

All comments

Jon Mark H

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

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

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

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.

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.