Whats the most reliable way to automate data collection by student/client name?

Dell55

I'm pretty new to Smartsheet and I have a pretty big set of data for each one of my 60-80 students I am trying to use it to track. I manage a therapy department at a special ed school.

I have two sheets one that gets raw data from a form filled out by multiple therapists for between 60-80 different students and another students specific info sheet with student profile, annual goals, treatment plans and service recommendations info. I want to be able to track how the raw data matches up with the treatment plans; like for example, I want to see if a student is on track to meet their goals and if we have provided the recommended number of treatment sessions for the year.

Currently, I automated it to copy rows of new raw data from treatment session raw data sheet daily and then create children rows in my info sheet for each student and use children formulas extract the data. This seems manageable as of now by I can Imagine the sheet might get too big if we continue.

I feel like there should be a way to sort the data by student name (by cross sheet referencing rather than coping the rows. For example, id like to get the number of hrs each kid has been seen to show in the completed treatment column of my info sheet and develop a trigger to highlight when a student is behind on treatment.

and because the caseload of students is shifting and changing pretty regularly I want to avoid formulas that I would need to edit every time my caseload changes. so far what have in terms of structure is a helper sheet; a student name list with a corresponding student code. when therapist fill out the form they click the drop down menu for the student code. I was able to get a Vlookup formula to return a consistent student name and code for each row of data so that its clear and spelling error don't cause errors for my sheets.

.

Anyone have a solution for this or a similar situation?

Thanks Dell

Andrée Starå

Hi @Dell55

I hope you're well and safe!

Yes, you're correct, and you could use cross-sheet formulas and SUMIFS/COUNTIFS or an INDEX/MATCH formula to collect information. A Report might also be an option.

Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

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!