For creating the Solo/Ensemble festival schedule, I use two documents. One is the one-page master schedule which lets me see the entire schedule at a glance, and the other is the Google Sheets master schedule. I had an administrator one year show me how to link different tabs (and even documents) to the master schedule so that they automatically update when I make a change on the master schedule tab. I was so excited to learn about this--before then, I would email PDFs to families, but then once any updates were made (which inevitably happens), those PDFs are no longer current and I'd have to send out a new one. Now families can access the link whenever they want and see the most up-to-date schedule. I cannot thank him enough for showing this to me!!!
The process of making the schedule takes a bit of preparation and time. First I enter all the entries into the Google Sheets master document, using ? for any students in a registered student's ensemble who I haven't received a registration form yet. I'll check in with those families individually the next week to see if they can participate or if I should delete their name from the schedule. If any members of an ensemble are also doing a solo, I am sure to add those entries next so that they're all grouped together before I start assigning times.
The different headings in the Google Sheets master schedule include Site, Time slot, Name(s) [first name only], Name(s) [full name], School, Grade, Title, Time Request, Adjudicator. (I keep the Site, Time slot, and Adjudicator blank until I'm actually assigning times.) I have the two separate columns for names because I'll use the full name on the schedules I post outside of the sites at the festival and to use mail-merge later on to create the rubrics and then the first name on the schedules I share with families to share privacy of the students a little bit (and hide the column with the full names).
To begin scheduling, I'll write in a few break times for adjudicators on the one-page master document, spacing them out so each adjudicator has their break during a different time slot. This one-page master document has 5 sites with 8-minute time slots running from 8:00 am to 12:00 noon, so 30 time slots per adjudicator, 150 time slots total.
Then I start assigning times to those with time requests first, being sure to add their names to the one-page master schedule and then type the corresponding site and time on the Google Sheets master schedule. Any students who are in more than one event get scheduled two or three slots away from their first time so that they don't have to be at the festival the entire morning. I don't schedule them any closer than two or three slots away in case their first room is running late and their second room is running ahead of schedule. I do want a little bit of a breather in-between for the student to regroup and focus on their next piece.
After the entries with time requests are scheduled (and any of those students' ensemble partners' solos), I look for students who are in more than one event and schedule them next, putting each of their events in different sites so they will get to work with more than one adjudicator. If there are any ensembles where only one member can attend, I plan to play with that student as their ensemble partner and then am careful not to double-book myself during a particular time slot.
Finally students who are only playing a solo, no ensemble, and have no time requests get entered last to fill in the spaces. When it gets down to the last few, I'll count the number of events and empty slots for each adjudicator so I can even it out as best as I can. Any super late registrations that come in after this point can get added to any empty slots until the schedule is filled.
Once the Google Sheets master schedule is completed, I go to Data, Sort range, then Advanced range sorting options. Be sure to check "Data has header row," then sort by "Time Slot" and then by "Site." Now I can look through the other tabs that auto-populate to see the schedule for each site, and the entries for each school from the beginning of the day to the end. I can't believe I used to do all those different sorts manually and then copy/paste into new tabs and then have to update all of those for any changes! This function is a life saver!
In this picture above of the Maple tab, I'm getting an error (where it says #N/A in A2) because in the query line, it says D='Maple', but in my master tab (below), the name of the school is really in column E, not D.
Once I change D='Maple' to E='Maple' in that formula, the entry from the Master tab with "Maple" as the school auto-populates into the "Maple" tab.
Here's what the formula looks like for the Site A tab:
For Site B, the last bit would say where A='B'"). I wanted the student's full name, not the first name for this tab, so up in the query line, I have "select A,B,D,E,F,G,H,I where A+'A'"--I skipped C, which is the column in the Master tab with the first name.
Once all that looks good, I do create separate Google Sheets schedules for each school's tab. In a new Google Sheet, I copy/paste the formula down below into the A1 box.
In the A1 box, here's what's inside: =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Eb3tXGMrsvrMWzRSdnb7tghBm9GpL2ouPDv8rk4HFp4/edit#gid=1276309528", "Maple!A1:N")
The http://.... is the URL of the Google Sheets master document. After the , " is the name of the tab I want, in this case Maple. This document auto-populates, and it automatically updates whenever something on the Google Sheets master document gets changed. I make sure the column with the students' full names is hidden on this document, as I will change the sharing permissions to "Anyone with the link can view." and I'll share this one with families and post on our class Canvas page. I also go to Format, Alternating colors to make this one easier to read. This is the file I will print and post in the orchestra room too.
For the festival, I print off a copy of the one-page master document for myself and then copies of the schedules for each site to place outside each room and to put in each adjudicator's folder. A copy of the schedule by school (and also by site) gets taped to the wall in the cafeteria, where students are warming up, so they can look if they need to check their time.
While I don't totally understand the formulas in Google Sheets, I can copy/paste from the previous year and make adjustments as needed to make sure everything is auto-populating as it should. Please let me know if there are any steps I should clarify or provide screenshots of!
