yogi_Set Up Conditional Formatting For Items Due inMoreThan3wks Due withIn3wks Due withIn2wks Due lessThan1wk PastDue Over30days

                                         Google Spreadsheet   Post  #1506
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by Teenie (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/uj02AZP9nus)

Originally created this spreadsheet in Smartsheet. Free trial is over so I have moved to Google.

My spreadsheet has a column for Date Received. This automatically calculates a Due Date of  +30 Days in the next colum over. I need each row to change colors based on the due date.

Here is a link to my spreadsheet https://docs.google.com/spreadsheet/ccc?key=0Amuta5sgdCuMdEY1UnA1UjUwZXBNUEFJYU00UU9EQnc&usp=sharing

At the top of the sheet you will see a color chart of how I would like my rows to change. (As you can see, the rows are already colored as they have moved over that way from Smartsheet) I need all new rows to take on this formatting as well.

I was playing around and trying to figure this out myself to no avail. Please use PO 3592 TA as a sample to play with. 

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

yogi_Pull Contact Name And Contact's Data1 through Contact's Data17 For Year Specified In Cell A15

                                         Google Spreadsheet   Post  #1505
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by virtuzol (https://productforums.google.com/forum/#!topic/docs/bpgodiACCag)
Multiple filtering
---
Hi Yogi, 

sorry for my inconistency. 

I have made for you a new COMMENTS sheet, and have written what you are expected. I hope that's clear.

https://docs.google.com/spreadsheet/ccc?key=0AlHjYxVqmzIRdDd5bWlmS2JQekFGUEtfeTZTMkhhSVE#gid=3

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

yogi_Pull Data From 'Form Responses 1' For Staff Of CEO (cell 5) With CEO_Staff Data In Sheet '0' Cells H12 to I

                                         Google Spreadsheet   Post  #1504
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-29-2014
post by Meredith Poynter (https://productforums.google.com/forum/#!mydiscussions/docs/7AYLl-Pk5nc)
How to lookup (using Index Small If Row) with multiple values, returning for all rows

I am working with this formula:

=ARRAYFORMULA(IFERROR(INDEX('Form Responses 1'!$A$2:$M;SMALL(IF('Form Responses 1'!$D$2:$D=$C$5;ROW('Form Responses 1'!$A$2:$M)-1);ROW(A1)),11),""))

$C$5 is a person's name on the spreadsheet.This formula looks that person up in 'Form Responses1'!D, as how ever many times it appears and returns the info (in the 11th column) from those rows. It works great where I need it to, but now, I'd like to have the formula look up multiple people's name and return all rows for each person. I've tried modifying the underlined section of the formula to have multiple values, but have had no success.

Please help. I haven't been able to find a solution upon searching. THANK YOU!

---

Here is the skeleton of my spreadsheet. https://docs.google.com/spreadsheet/ccc?key=0ApOYbVKFbSMNdHVKMVIwb1lROEFORkdjd2Q4YXNpVFE&usp=sharing

I'm looking for formulas to go in "Customer Report" Rows 21 and down. I want them to pull the information from "Form Response 1" for the CEO and any Staff that works for that CEO. Currently, the formula only pulls data for the CEO. 

As the current selection of "John B" for CEO on the Customer Report" it should show the information in Row 21 as it currently shows, and then also show information from row 4 in "Form Response 1" as Ashley P works for John B. 

On the "0" tab, I created a table that will always populate a column with the CEO selected at the top of the "Customer Report" and the CEO's staff, to help with the process. My thinking was to have the formulas in the "Customer Report " rows 21 and down to use '0'!J14:J21 as the values to lookup but couldn't figure out how to do it, if that is even the right thinking to do it. I can not have the form response have the CEO and staff in different columns - that would be too easy!

Thanks for your help!

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

yogi_get count of each of the items listed in cells B30:B34 in each column of range C4:I26 Marked with red chain dotted line

                                         Google Spreadsheet   Post  #1503
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-28-2014
post by Matthew Eisenberg (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-s9-Qd2xAoI)
Assign a numerical value to text
---
this problem is not about assigning numerical value to text ... it is for getting count of text items
and then summing the instances of text items encountered

yogi_Set Up FORMULAS for WEEKDAYS between Starting Period Date and Ending Period Date

                                         Google Spreadsheet   Post  #1502
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by NetEngineer009 (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/KahpovREU1s)
How to make Work days auto propagate in a mothly calendar
So this is my first post in this forum so i hope im in the right place.

I need help making days auto propagate based on a real time month calendar year. For example if i want to make a 2-3 month span of Jun-Jul 2013 for example this is what i want:

JUN 2013																				JUL 2013
WEEK 1					WEEK 2					WEEK 3					WEEK 4					WEEK 1					WEEK 2					WEEK 3					WEEK 4					WEEK 5
3	4	5	6	7	10	11	12	13	14	17	18	19	20	21	24	25	26	27	28	1	2	3	4	5	8	9	10	11	12	15	16	17	18	19	22	23	24	25	26	29	30	31

thats all manually typed in but i want to use a function to type in the first day and have the rest fill in.

at first i used this but then just got the days with the next one below
=ArrayFormula( text( date( year(A6) , month(A6) , day(A8) + column(A1:AQ1) ),"m/d/yyyy") )

this one i used to put the first day of that month and it auto fills in the range i set.
=ArrayFormula( text( date( year(A6) , month(A6) , day(A9) + column(B9:AQ9) ),"d") )
I fill in 3 then in 4's spot i put this code and it fills from 4 on to end range, but it does every day of the month (including weekend dates)
3 4 5 6 7 8 9 10 etc..

this formula does NETWORKDAYS which is work days mon-fri

=IF(WEEKDAY(A25)=7,A25+2,IF(WEEKDAY(A25)=6,A25+3,A25+1))

but this requires it to have a full date (6/20/2013) in the starting cell to the left of it then click and drag to all the cells you want to the right
also i dont know how to just get the day # instead of the full date m/d/yyyy

Is there a way to mix the 2 of these formulas?
The first formula is the one i want to use because it auto fills days to the right of it without having to click/drag and it also just outputs the day value # but i need it to only output WEEKDAYS workdays mon-fri
Is there a way to make that formula work for what i need?

Thanks for the help :)
-------------------------------------------------------------------------------------------------------------------------------------------------

yogi_From A Table Of Names And Details Of Various Items Rearrange Data By Name And Items As In A Database List

                                         Google Spreadsheet   Post  #1501
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jan-27-2014
post by Jeremy Booth (https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/OXn5epLkb4U)
Transposing some columns and duplicating others

Hi

I have some data collected from a google spreadsheet form. The form asks for various details but includes a name and then a couple of pieces of data about up to 4 items. In the spreadsheet this all goes in as a single row. We do various data processing exercises with this data that suits the one row per form. There is one piece that we would like to do that doesn't, and for that data I would like to transpose some data. What we would like is each item with it's data on a row with the name as the first column, so if a use fills in data for all 4 items they would have 4 rows with 3 columns, name, item and details. We would like this as a formula in the sheet so that as new forms are filled in, the data is automatically updated as it is for the other representations.

The data is sensitive so I've knocked up a test sheet here. 

We want to turn

Name	Item 1	Some data about item1	Item 2	Some data about item 2	Item 3	Some data about item 3	Item 4	Some data about item 4
Fred	car	VW	motorbike	BMW	bicycle	rusty	shoes	worn out
Wilma	tram	relaxing	boat	sedate
Bill	train	late	bus	late	car	expensive

into 

Fred	car	VW
Fred	motorbike	BMW
Fred	bicycle	rusty
Fred	shoes	worn out
Wilma	tram	relaxing
Wilma	boat	sedate
Bill	train	late
Bill	bus	late
Bill	car	expensive

I have had a search around and found functions that look useful (like transpose), but I'm no spreadsheet expert and I've not been able to think of a way to combine them to get the data we want.

Thanks

Jeremy

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

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

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