Tuesday, December 11, 2012

yogi_Sort Combined Data In Columns Imported From Another Spreadsheet Plus Manually Added Columns

                                          Google Spreadsheet   Post  #922
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Dec 11, 2012
user Wish GD was as easy as Excel said:(http://productforums.google.com/forum/#!mydiscussions/docs/GTBKcMtiGQk)
Sorting rows after an Import Range 
I am a moderate user of google docs and have a fairly good understanding of formulas.  I am running into some issues with a sorting.  I am using a Mac.
I have a Workbook A where a group inputs information for people to review.  In Workbook B, I am using the formula =ImportRange(A1, "StudentList!A1:E"). In a second sheet of workbook B, I sort the list according to name using the formula =SORT('TeamK'!A2:E,2,true). All works fine with this set up.

In workbook B, a second group of people add data to columns F though I. Here is where the problem occurs. When more information is added in Workbook A, the columns F though I do not adjust.

Is there a way to have a fixed row sort? (not sure if that accurately describes it)

If you need more information or clarification, please respond. Any help is appreciated.
n Workbook A - Titled "Team G DONOTUSE Agenda/Minutes", teachers input student data into the "Team Agenda" tab.  This is a repeating template in which I pull out all the 'Student Concerns' into the tab "StudentList".  This gives me a list of all the students that this team has talked about.  So in this brief example on the StudentList tab you can see that Bob C. was discussed at the meeting on 9/12/2012 and 9/25/2012 (Highlighted in Yellow).  

From here, I am using an Importrange to get the data from the Team workbook to the Admin workbook.

In Workbook B - Titled "Admin Sheet - GD Submit", there are two tabs "TeamG" & "TeamGsort".  The data from Workbook A - "Team G DONOTUSE Agenda/Minutes" comes into Team G tab, and then another group reviews the data and adds to columns F through I.  The problem is as the school year has gone on the list of students is too long to manage, so we need to sort by student.  The reason for this is when the second group enters information they need to be able to see what they previously entered.  In my example the data entered for Bob C. in "TeamGsort" was '2, B' & '3, C, Next level'.  Everything is fine until another name is entered into Workbook A.  When a new name is entered in the "Team Agenda" the data that was entered into columns F through I will not sort with the name that it was entered with.  

I want to be able enter in my data into the "TeamGsort" in columns F through I AND have that data stay with it's original row.  In the "Team Agenda" tab try entering in the green highlighted row, "Barry B.; Beh; Talking" in those columns and see the result on the "TeamGsort" tab (On the Admin Sheet).  You will see the data in columns A through E sort correctly, but columns F through I do not sort.  Is there a way to make this happen?
following is a solution to the problem