It all began with Chenny's request for my assistance in sorting out a three-page name list which he scribbled notes all over the blank areas about the class time changes, students contact details and etc. the notes were so messy that even Chenny was not sure what some of the notes were about.
It was not a big task. Before long I had it finished by putting those notes in a Google spreadsheet. But by the time I was going to give it back to him, I was curious to know if he had any administration system in place to help him managing his music teaching job. All he showed to me was a timetable which sets the week days on the top row and a time with an increment of 30 minutes ran on the first column starting from 9 AM till 9:30 PM, and about 30 students names were scattering over the grid of the table. This table serves to tell him the class time of his student. Other than this timetable, is the three pages of a computer print which the music Center gives him for the purpose of contact with students. As time goes by these three-page prints became very messy, and Chenny was the only one who could get information out of them, but I believe even him would find it hard. I think that was why he asked me to retype it.
I vaguely know that database is the answer, but I aim at making it possible to access on the Internet so that Chenny can refer to his database through his iPhone when he is on the road. So I searched for the functions on the Web and landed at the webpages of Google docs.
I knew Google docs contains spreadsheet application, but I had only learned how to use the most basic formulas like addition, subsection, modification and division. After some searches, I found that the functions I had in mind had also been asked by many other Google docs users in the help forum, so I knew I was on the right direction of my adventure in the exploration of creating a working system for Chenny.
First of all, I created the main worksheet which I labeled it "Student". This is for Chenny to enter their contact details, class time, class day, fee to charge. Then the challenge came when I created the second worksheet labeled as "Time window".
In the "Time window", my idea was placing a formula in each of the cells that was able to grab the data in the column of class day and class time in the "Student" worksheet and to place the student's name in the corresponding cells in the "Time window" worksheet.
It took me about two weeks in reading many many relevant posts in Google's help forum and playing with the formula which I thought should work but always ended up with a return of "N/A", "ERROR", "REF" or anything but not what I wanted. Gradually my frustration was rising and my patience was running out, suddenly the right data turned up in the cell when I pressed "enter". That feeling was wonderful. It was like you had just won a big prize of Lotto.
For almost one whole month, most of my time day and night were spent in the exploration and
experiment of the formulas. Due to this sudden change in my everyday living style, I spent very little time on the Internet. Some of my friends noticed this change, e-mailed and asked me why I was suddenly so quiet on the Web.
Once I had sorted out the first working formula, it became easier to interpret the strange language used in the formula. Below are some of the formulas which I figured out after innumerable experiments:
=sort(filter('Lesson log'!A2:K,'Lesson log'!J2:J="y",'Lesson log'!I2:I<='Lesson log'!G2:G,'Lesson log'!A2:A>=(today()-14)),2,true,1,true)
=count(iferror(filter(Student!A2:A,Student!F2:F=E4,Student!G2:G<>""),"0"))
=iferror(iferror(filter(Student!A:A,Student!F:F=F1,Student!G:G=A15);filter(Student!A:A,Student!F:F=F1,Student!G:G=A14,Student!H:H=2)),"")
=index(filter(C1:C243,search(H34,B1:B243)),1)
By the end of November, I had accomplished a Google spreadsheet that was able to accommodate Chenny’s need for queries, information check, statistics of his earning. And he finds it helpful, and I feel I am still useful.
