Synchronizing Sheets and Master Sheet

Question:

(by Shetal)

Ok... so the reason I'm writing to you is because I need some Spreadsheet expertise.
I have a spreadsheet that I need help with.
So in this spreadsheet the first tab is the grade master which have my students names, info, etc.  The rest of the tabs are the different 5th grade classes.  I was able to filter the students from the Master into the individual classes by the class code.
THIS IS WHERE I AM STUCK...
Each teacher will eventually enter in data in their class sheet and what I need to happen is that the data they enteredautomatically filters into the grade Master sheet so I can see all of this data at an entire grade.  Any ideas?
I have protected the master sheet as well as parts of the individual class sheets because of the formulas I have in there. With 200 students in the master it would be annoying for the teacher to find each of their students to enter in their data that's why I have used a query formula to filter out the students as well as some demographic data that I want in each individual class sheet. Below is a sample 5th grade spreadsheet. Hope you can figure this out. I'm not very good with scripts and have been stuck on this for over a week.

Solution:

Please check also check out the solution by option 2:
http://igoogledrive.blogspot.in/2012/09/Synchronizing-Sheets-and-Master-Sheet-option2.html


First of all, you need to insert the following script in your script editor:

function VMerge() {
  var maxw=l=0;
  var minw=Number.MAX_VALUE;
  var al=arguments.length ;
  for( i=0 ; i<al ; i++){
    if( arguments[i].constructor == Array )l =arguments[i][0].length ;
    else if (arguments[i].length!=0) l = 1 ;
    // literal values count as array with a width of one cell, empty cells are ignored!
    maxw=l>maxw?l:maxw;
    minw=l<minw?l:minw;
  }
  if( maxw==minw) { /* when largest width equals smallest width all are equal */
    var s = new Array();
    for( i=0 ; i<al ; i++){
      if( arguments[i].constructor == Array ) s = s.concat( arguments[i].slice() )
      else if (arguments[i].length!=0) s = s.concat( [[arguments[i]]] )
    }
  if ( s.length == 0 ) return null ; else return s        //s  
  }
  else return "#N/A: All data ranges must be of equal width!"    
}

And now, first of all, create a new sheet and insert try the following formula in it:
=query(VMerge('5-300'!A8:BD;'5-302'!A8:BD;'5-304'!A8:BD;'5-305'!A8:BD;'5-307'!A8:BD;'5-309'!A8:BD);"select * where Col1 is not null order by Col1")

Then you will get the Idea of what is happpening...

Now, you need to select the Columns in query that you need that from "Column N" to "Column BD" that is from Col 14 to Col 56

So here is the formula (You need to put it in Cell N8 in Master Sheet):

=query(VMerge('5-300'!A8:BD;'5-302'!A8:BD;'5-304'!A8:BD;'5-305'!A8:BD;'5-307'!A8:BD;'5-309'!A8:BD);"select Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30, Col31, Col32, Col33, Col34, Col35, Col36, Col37, Col38, Col39, Col40, Col41, Col42, Col43, Col44, Col45, Col46, Col47, Col48, Col49, Col50, Col51, Col52, Col53, Col54, Col55, Col56 where Col1 is not null order by Col1")

Note: You need to change the following formula in all sheet in "Cell A8"
=query(Grade5Master!$A8:$N,"select A,B,C,D,E,F,G,H,I,J,K,L,M,N where D = 506")
to
=query(Grade5Master!$A8:$M,"select A,B,C,D,E,F,G,H,I,J,K,L,M where D = 506")

Following is the screenshot of Sheet "5-300":

NOTE:
The above solution will work only when you have Student Id all in sequence in Master Sheet, as I have sorted the query in Cell A8 of master sheet, so it will return in ascending order...

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

http://igoogledrive.blogspot.in/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan,

iGoogleDrive