yogi_Given Student ID Extract Quiz1 And Quiz2 Data -- Quiz Scores Reside In Different Spreadsheets

                                          Google Spreadsheet   Post  #1483
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-12-2014

original post by Graham Stevens ... question by mcjtom (http://productforums.google.com/forum/?zx=yzzf0q7ec9cg&usp=sheets_web#!category-topic/docs/spreadsheets/qob46a0Y524)
I've asked this question in another context, but I'd like to make sure that this is not really doable - sorry for repetition:

There is one spreadsheet, Master Roster, that contains student's IDs

There are other spreadsheets, assignments, that contain some of those students IDs and grades attached

I'm trying to semi-automate bringing assignment grades to Master Roster by matching the IDs.

The way I do it now is to:

1) make a Helper sheet in the Master Roster spreadsheet in which I use ImportRange to pull IDs and grades from assignment spreadsheets (I need one Helper sheet for each assignment): e.g.

=ImportRange(Quiz01;"Grades!B8:J208")

where Quiz01 is a named range containing quiz spreadsheet ID number.

2) use Vlookup in a column in Master Roster to match student IDs with those in the Helper Sheet and pull the assignment grades to the Master Roster: e.g.

=ARRAYFORMULA(IF(ROW($A:$A)=1,"M5/M6 Quiz 1",IFERROR(VLOOKUP($A:$A,'Helper_Quiz_01'!$A:$I,9*SIGN(ROW($A:$A)),FALSE),"0")))

Vlookup does not accept another spreadsheet as an input (nor implicit ImportRange), hence the need for Helper sheets.

But Query can accept implicit ImportRange, so my hope was that I could do the same thing in one go (i.e. in one formula, without Helper sheets. But I can't figure out how to write the query so that only records with matching student IDs are pulled to the Master Roster.

Here is a simplified system that illustrates what I'm trying to do:

https://drive.google.com/folderview?id=0B61zXygcrKXWSXhSSVRaNzVJREE&usp=sharing

It kind of works, but it's quite finicky, especially if I'd like other teachers to use it. If I could avoid Helper sheets, and include all the variables in a single formula that would simplify it significantly.

One more note: I realize that if the Grades were in the sheets of the Master Roster, rather than in separate spreadsheets, that would simplify things significantly (no need for ImportRange and Helpers), but it's not that easy to do (I use Forms and Flubaroo script to get the grades first).

Is there a way making it simpler (from the user's perspective, not necessarily formula complexity...)?

Cheers and thanks!

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