Sunday, February 17, 2013

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

2 comments:

  1. Note: In order to use ImportRange, you need to have been added as a viewer or collaborator to the spreadsheet from which ImportRange is pulling the data. Otherwise, you'll get this error: "#REF! error: The requested spreadsheet key, sheet title, or cell range was not found."

    I cannot have the students have access to the master spreadsheet where the data is coming from and they have to according to this right?

    ReplyDelete
  2. Hi Mark:

    Students don't need access to the spreadsheet from which the data is imported ... the teacher is importing the data into the student Grade Sheet. I suggest you think the process through on how you want to facilitate the student reading his/her grade.

    Make It A Great One.
    Cheers!
    Yogi
    Cloud Computing -- Google Docs Way
    yogi--anand-consulting.blogspot.com

    ReplyDelete