yogi_From Form Responses Sheet Split Student Data And Grades For Assignment By Date Into Sheets By Teacher Name

                                         Google Spreadsheet   Post  #1500
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Christopher Salem (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/nB15rBNrn1Q)
Import Range & Query Function for Classroom Gradebook

Hello,

I'm an elementary teacher trying to simplify my life by automating some parts of my classroom management with Google Drive.

Currently, I have students submit some data (spelling test scores, homework answers, etc.) using a Google Form. This dumps all the data for me into a spreadsheet. Right now, I'm cutting and pasting this manually into my grade book. I would like to automatically import the data from the Form Responses sheet into my gradebook sheet. I believe I am trying to use the ImportRange and Query functions to do this. However, I cannot get this to work correctly.

Because a student might submit multiple times to a form (multiple homework assignments or test, etc.), I need to be able to get the function to pull data for a particular student, and then pivot the data into new columns (so each assignment is in a separate column).

Currently my formula looks like this:

=query(importrange("Sheet14!A1:E2000"), "select Col5 where Col3 contains '1' pivot Col5 ORDER BY Col1", 1)

Column 5 is the test score. Column 3 is the Student's ID Number (I was trying to use Student #1 in my test) and Column 1 is the Timestamp. The message I receive is error: Invalid query: Cannot use PIVOT when no aggregations are defined in SELECT.

I have tried a couple of different things to aggregate in my SELECT, but to no avail. When I remove the "pivot" entirely, it says Col5 does not exist in this table.

Second question, I would like to somehow alter the formula so that I do not need to retype the formula for every student, replacing their ID number, but rather have the function automatically fill for each unique student (Putting all of Student 1's data in Row 1, Student 2's in Row 2, etc.) so I can see my whole class in one sheet.

My understanding of how to do this is basic at best, but I'm a quick learner! Thanks for any help or let me know if I can give more information or clarify anything here.

Thanks!

---

Yogi,

Thanks for your reply!

I created a test spreadsheet that mimics what I am trying to do.

In the spreadsheet, there are four sheets. The first three are for each class that I have during the day. The fourth sheet is the Form Response sheet that is automatically populated when students submit their scores.

What I want to do is take the data in "Scores" column from the sheet "Form Responses 1" and automatically fill it into new columns in the appropriate homeroom grade sheet.

I want the spreadsheet to recognize a kid (based on their Student Number and their Teacher name) and then pull their score and put it into the correct spreadsheet. So, for the scores that were submitted on 11/21/2013, I want those to be in a new column that is labeled "11/21/2013". Or, if it is better, I can add a new column to the Form Response for the name of the assignment, and then they would be labeled from that.

For example, in the sheet "Salem Homeroom", cell D2 should read "10", because that is the score that the Student Ben earned from his 11/21/2013 assignment. Cell E2 should read "5" because that is what he earned from the assignment on 12/5/2013.

Thank you! Please let me know if this is not clear or if you need more information. The sheet I shared is a test sheet, so feel free to make any edits or changes to it.

Thanks,

Chris

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

yogi_count Instances By Matching DEPARTMENT And MONTH And YEAR In Another Sheet

                                         Google Spreadsheet   Post  #1499
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Nick Depratto (http://productforums.google.com/forum/#!mydiscussions/docs/L02CQBQoi2Y)
Countifs with multiple date and name ranges

Hello, 

I have a sheet that i would like to pull data from but with names and dates. I would like to know how to count when one row with a certain name has been entered into the sheet in a certain date range. 

thanks in advance...

---

Here is a link to a editable sheet. 

https://docs.google.com/spreadsheet/ccc?key=0Am7X0hxg0iyBdEZwNXpoRm1RSlNmNzl1Y09uWFdmWlE&usp=sharing

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

yogi_Conditionally Format Cells D3 to D6 (Background green) If Value In Cell Is > Value in Cell A1 of AnotherTab

                                         Google Spreadsheet   Post  #1498
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-26-2014
post by sgupta1717 (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/chrome-browser/i-B4f00LEuI)
Reference a different tab in conditional formatting

Hi.  In the new Google Sheets, is anyone able to reference a cell in a different tab when doing conditional formatting?  This is what I want to do:

Custom formula:  =D3>AnotherTab!$A$1

Format:  Make the background green

Range:  D3:D6

However, I get an "Invalid Formula" error whenever I try to reference a different tab.

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

yogi_From A Table Of Names Addresses And Multiple Years Extract Names And Addresses For Specified year

                                          Google Spreadsheet   Post  #1497
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-26-2014
post by Kft Penzhid (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/how-do-i/chrome-browser/bpgodiACCag)
Multiple filtering

I have a sheet named 'Data' like this:

contact name	contact address	contact date1, year	contact date2, year	contact date3, year
Joe	Budapest	2007	2011	2012
Gabe	London	2011	2011
Jorgos	Athen	2012

I'd like to collect contacts by contact date. IMPORTANT: I have multiple dates in multiple columns to one contact. In case there's multiple dates I need that contact multiple times in my new sheet. For expample filtering the year 2011 I need this list: Joe, Gabe, Gabe. Additionally I need the contact addresses, too.

So I have tried this:

=VMERGE(FILTER(Data!A2:B;Data!C2:C=2011);FILTER(Data!A2:B;Data!D2:D=2011);FILTER(Data!A2:B;Data!E2:E=2011))

where VMERGE is a script that merges the results of the three FILTER function in one.

But Google puts me an error: #N/A All data ranges must be of equal width!

Any idea?

Could this solve with a single FILTER function called for filter all of my date columns?

Thanks

Z.

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

yogi_Count Instances Of Clips From Unique Entries Made Up By Concatenating Palylists!C2 Through D

                                          Google Spreadsheet   Post  #1496
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-25-2014
post by Simon LEFEUVRE (http://productforums.google.com/forum/?zx=tu8u6x37u114&usp=sheets_web#!mydiscussions/docs/vZ8Q0cF5ljc)
How to use COUNTIF with string containing "?" or "~"

Hi!

I'm using the function COUNTIF on a range of cells which contains text.

But some strings contains the characters "~" or "?" and the function COUNTIF seems to interpret these strings like wildcards and results are 0 (zeros)

Some example :

a cell (A1) contains the text "Spring ~A B °C~"

in an another cell (B2) I put the command =COUNTIF(A:A;A1) and it returns 0

It seems that the character "~" in the string is considered as a wildcard.

How can I fix this? 

---

Hello Yogi!

Thanks for your help!

So the spreadsheet is here :

https://docs.google.com/spreadsheets/d/1W_YqidRlT7eIV8cEtSE3Lw_B82MDa6zm_Ve89ZEsPyw/edit?usp=sharing

The datas are in the tab named "Playlists"

The column G is generated with concatenation of columns C and D for each line.

This colums is sorted to have only one item of each entry in the tab "Clips"

What I want to do :

In front of each entry in tab "Clips" I want to show the number of time it appears in "Playlists"

So I used the formula :

= COUNTIF(Playlists!$G$2:$G; $A2 )

to do this. But I remarked entries with "~" in the string have zero as result... :(

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

In regard to handling wild card characters ? and * and the character ~ as literal characters one has to precede them with another ~ in the search string. In Simon's data I only noticed use of ~ character only -- and that is why I have addressed handling ~ as a literal character in the solution here. In case there are ? and * that have to handled as literals these can also be handled in a manner I have addressed the issue of ~.

yogi_In New Google Sheets Compute Sum Of Last N Non Blank Entries (Text Or Numeric) In A Column Or A Row

                                          Google Spreadsheet   Post  #1495
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-25-2014
post by laakkus question by Vojtechm (http://productforums.google.com/forum/?zx=ekyp0ne11nui&usp=sheets_web#!category-topic/docs/spreadsheets/cugQOp_Hrqc)
How to get the sum of last 5 not empty cells in a column in google spreadsheets?
Hi again Yogi,

Do you have any idea why your solution is not working any more in the New Google Sheets? Do you know what to change to make it work even in the new version?

Thanks,

Vojtechm

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

yogi_Compute The SUM of Lowest Four Numbers In Cells D6 to D15

                                          Google Spreadsheet   Post  #1494
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-22-2014
question by michaelabendroth (http://productforums.google.com/forum/?zx=vrs81mo04d67&usp=sheets_web#!mydiscussions/docs/FoMR-ILCf-M)

Hi,

 

I am trying to sum the 4 lowest numbers in a range (that are in a single column).

 

Conceptually, I am administering a golf league where the sum I am looking for is the total team score, consisting of the 4 lowest numbers of that team's single column.

 

I am having trouble in some way with the formula "=sum(small(D6:D15......." and how to reference the 4 lowest numbers.

 

Thanks in advance.

---

Unfortunately I'm still having trouble.

 

I neglected to mention that in some cases, the lowest numbers can be negative.  (ie. "under par")

 

https://docs.google.com/spreadsheet/ccc?key=0AroHKqNgDYP7dGF0S25yLVotbENiVjItSWhIMkQ1X1E&usp=sharing

 

This link is to my sheet. 

The cell highlighted red is my proposed formula cell.  (I manualy did the math, only populating the cell with my desired number, 63 in this case).

Yellow is the column range, that I am looking for the 4 lowest, including negative numbers, where of course -5 is lower than -2, for example.

 

The red cell should equal -63 in this case.

 

Thanks kindly,

 

Mike

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