Making a Student Lookup System

Over the Winter Break, I was possessed. I spent too much time analyzing the data for our Inclusion [Support] Team. I did one thing, then thought of another, and xxx hours later, I had an awesome thing. It was exactly what I needed. 

Then I broke it.

Now I am trying to recreate what I did and I cannot remember how I set up the formulas. I am going to try to keep notes here for the future.

In the Student Lookup, you would choose the section, or programme (PYP, MYP, DP), then that would filter the Grades in each section. The grade would then filter the student names in that specific grade. 

Below are the steps I took to make it on the dashboard page. 

1. Get a UNIQUE list of students
2. Find their Grades
3. Count the number of academic vs behavioral concerns
4. Find the student section - =INDEX(FILTER('sheeetname!$C$2:$C, 'Form Responses 1'!$E$2:$E = B5), 1)
5. Find the concerns  - =SPLIT(TEXTJOIN(",", TRUE, UNIQUE('sheetname'!J2:J)), ",")
6. Flatten the row =TRANSPOSE(UNIQUE(FILTER('sheetname'!J2:J, 'sheetname!J2:J<>"")))

This is the backend. 

I made a column with the sections, PYP, MYP, DP. I then used this formula in the next column, the middle column above, 

=SORT(UNIQUE(FILTER(C3:C, SEARCH('sheetname'!C25, B3:B) > 0)), 1, TRUE)

And then the third row:

=IFERROR(SORT(FILTER(D3:D, C3:C='sheetname'!D25)), {})

When I looked at it, I thought maybe individual students is not as good as showing a whole class. This could be useful for specialist teachers as well as subs, so I adjusted the above formula to stop at the grade level and I got the below output: