yogi_Import Student Grades From y2013 Global Studies Gradebook Master Spreadsheet For Open Ended Column Range

                                          Google Spreadsheet   Post  #1033

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Feb 17, 2013
user Mark Gunkel and question by Sean Abbott-Klafter said:()

Teacher wanting to use online spreadsheet for grading

Hi Yogi!

I a, new to databases and need help with a importrange formula on the student spreadsheets I am creating:

I have created the master spreadsheet with all the data and am working on the model for the student spreadsheets. You can see a copy of the model here: https://docs.google.com/a/bronxcompass.org/spreadsheet/ccc?key=0AgNZUUk6dHWvdHdyRm9CQnpBdjRSQ21zS1RyRGhYRkE#gid=0

My problem is the following: The "Sean Abbott-Klafter Global Studies Grades" sheet uses importrange functions to pull data from the master sheet specific to that student. The front page on this sheet works perfectly. My problem is on the sheets that breakdown performance in the grade subcategories. For example look at the "quizzes" sheet. Currently my importrange function only imports a static range of cells from the master sheet. So when I add a new quiz to the master sheet the importrange function does not capture the new data. This then causes obvious problems on the sheet you are looking at. So my essential question is how can I make the importrange function dynamically increase as I add new columns to the master sheet. Is the answer with a query function that dynamically modifies the range in the importrange? The problem is I am getting confused with the language I need with "select" and "where" etc. Could I have a query that look across a row on my spreadsheet for a blank cell and then stops at that cell? Something with <>?

Then along similar lines I need to modify the transpose function in F6 of the quizzes sheet to increase dynamically. 

Note: I want to find a solution that address the issue of increasing dynamically. I know I could simply make the importrange function import a larger range of static cells and the extras would just be blank.

Any help is much appreciated!!!!!!!!!!!!!

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

following is a solution where I have used two helper cells A3 and A4

and here is spreadsheet y2013 Global Studies Grade Book Master