yogi_Query Teacher TimeSheet Data To Tabulate Attendance On A Range Of Dates By School

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #756   Sep 17, 2012     www.energyefficientbuild.com.

user foreright said: (http://productforums.google.com/forum/?zx=lkth9hlcc6om#!category-topic/docs/spreadsheets/gRcQ9xztuC4)
Query time sheet
....
This is a link to some sample data:

https://docs.google.com/a/arabcityschools.org/spreadsheet/ccc?key=0AqeLnNOb-ur5dERMZzNMV3hNTVlVR2pCazVaTzBpM2c

Our employees have to sign in every morning using a google form. The purpose of signing in is so each of our 4 schools can reconcile between the sign in sheet, substitute teacher form (which is on paper at each of the schools) and ensure that sick/personal leave paperwork has been completed. The reconcile process happens once a month at the end of each pay period.

What I am trying to accomplish is to provide each of the 4 schools an alphabetical list of each employee that are assigned to their school. Ideally the report generated would allow a date range to be selected.

....

I've edited both documents 'sample sign in sheet' and 'sample sign in sheet results'

The 'sample sign in sheet results' only has two sheets (ultimately it would have 4 sheets, one for each of the schools) and the data would be pulled from 'sample sign in sheet'. Hopefully that is sufficient enough to convey the desired result.

In the 'Sample sign in sheet results', each user appears on a single line with the daily timestamp pivoted 

In the 'sample sign in sheet' you will notice that Michael Shipp did not sign in on 8/16/2012 and therefore there is a blank cell next to his name under the 8/16/2012 header. 

This will allow the secretaries to easily see that Michael Shipp was absent and allow them to verify that the proper sick/personnel leave paperwork has been filled out. 

The attendance sick leave verification process happens once a pay period and is never a consistent length of time. Therefore, ideally the 'Sample sign in sheet results' would allow for a date range to be applied. So the secretaries could enter the first date of the pay period and the last date of the pay period and the returned result be all of the submitted data between and including the two dates entered.

----------------------------------------------------------------------------------------------------

following is a solution to the problem

I imported the data from the Database in sheet yData of the spreadsheet for the schools to minimise the use of ImportRange function calls, so that the data could be used over and over for each school without having to invoke the ImportRange function numerous times.

The sheet yData may be optionally hidden.

here is an image of the spreadsheet housing the Database: