Filtering Data of Master Sheet

Question:

( by tony.silveira )

all,

 

I have a form sheet that 2 employees use to input data into a master record.  then i have separate worksheets for each of the 2 employees to track their custom data for each customer.

 

My question, is it possible to pull specific client data into a worksheet based on who input it.  As an example:

 

My Spreadsheet has 3 worksheets:

"master"

"tony"

"sergio"

 

all data is input into the "master" sheet and both Tony and Sergoio enter their name when inputting a customer, so:

 

"customer A" was input by "sergio" and his name is in column A

"customer B" was input by "tony" and his name is in column A

 

so based on "column A" (either tony or sergio), "customer A" will only appear on "sergio's" worksheet.  "customer B" will only appear on "tony's" worksheet.

 

any thoughts?  many many thanks up front!

Solution:

Have a look at the following screenshot of Sheet 'Master' of my Spreadsheet:

And following is the screenshot of Sheet 'Tony':

I have the following formula in Cell A1:

=filter('Master'!A:E;'Master'!A:A="Tony")

And following is the screenshot of Sheet 'Sergio':

I have the following formula in Cell A1:

=filter('Master'!A:E;'Master'!A:A="Sergio")

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 and confidential projects:

http://igoogledrive.blogspot.com/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

Google Spreadsheet Sumif Example

Question:

( by hrvooje )

What I'm trying to do is:

   A   B   C

1  X   2   100

2  X   4   200

3  Y   6   300

4  Z   8

5  X   9

If a cell in a column A contains x add 2 to column C. The result should be 102.

If a cell in a column A contains y add 6 to cell C2. The result should be 204.

... and so on

(x, y and z are words)

At the end, the sheet should look like this:

   A   B   C

1  x   2   115 --> ( 100 + B1 + B2 + B5)

2  x   4   206 --> ( 200 + B3)

3  y   6   308 --> ( 300 + B4)

4  z   8

5  x   9

OS: Windows 7

Browser: Chrome

Solution:


Have a look at the following screenshot of my Spreadsheet:

I have the following formula in Cell C1:

=100+sumif(A:A;"X";B:B)

and following formula in Cell C2:

=200+sumif(A:A;"Y";B:B)

and following formula in Cell C3:

=300+sumif(A:A;"Z";B:B)

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 and confidential projects:

http://igoogledrive.blogspot.com/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

Google Spreadsheet Advanced Query Solution

Question:

( by Joseph Barry )

Hello,

 I am a novice user trying to automate an job applicant evaluation spreadsheet to update when interviewers submit a form response over email. The form contains questions for who is interviewing, who is being interviewed, and what your rating is 1-10. The Form response sheet shows me the time stamp, the interviewers name, the job applicant's name, and the rating. Each interviewer may be submitting multiple entries for multiple applicants. I want Sheet 1 to update with the most recent submission by each interviewer for each job applicant. I have tried 

=filter('Form Responses 1'!$D:$D,'Form Responses 1'!$B:$B=B$1,'Form Responses 1'!$C:$C=$A2,'Form Responses 1'!$A:$A=max('Form Responses 1'!$A:$A))

but this only gives me the most recent submission from among all of the interviewers, rather than each interviewer's most recent assessment of each applicant. Can't figure out how to tie the max function for the timestamp in with the two other variables. Help please?

Here is the spreadsheet:

Solution:


Have a look at the following screenshot of 'Sheet 1' of my Spreadsheet:

I have the following formula in Cell A1:

=query(arrayformula(iferror(vlookup(query(query('Form Responses 1'!A:D;"select max(A),B,C where B<>'' group by B,C");"select Col1 offset 1";0);'Form Responses 1'!A:D;{1,2,3,4}*sign(row(A:A));false)));"select Col3,max(Col4) where Col2<>'' group by Col3 pivot Col2")

And following formula in Cell E1:

=arrayformula(if(row(A:A)=1;"Average Rating";if(A:A="";"";iferror(round((B:B+C:C+D:D)/3;2)))))

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 and confidential projects:

http://igoogledrive.blogspot.com/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

Google Spreadsheet Group By Queries

Question:

( by William Ogle )

So here is my situation. I would like very much to be able to use data that is collected through a form, then dumped into this spreadsheet, to generate reports based on when the data was entered. Here is an example row from my spreadsheet. 

Timestamp

Work Order

Service Level

Service Team

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

1/1/2013 10:12

555678

24 Hour

Alpha

General Store

John Smith

I have changed most of the information for privacy sake, but the concept is the same. The form I use is set up as a 6 page form. On page 1, it asks for the work order, service level, and service team, then based on the selected service team, it will send the user to one of the pages. Each team gets its own page, because each team has different project coordinators and different customers. That is why you see customer and project coordinator repeated 5 times. Each sheet created 2 columns of its own. Lets say that team Gamma goes to page 2, team Beta goes to page 3, team Alpha goes to page 4, team Theta goes to page 5, and if the users selects "other" as the team, it goes to page 6. You can see a little more detail about how this works below. 

Timestamp

Work Order

Service Level

Service Team

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

Customer

Project Coordinator

1/1/2013 10:13

555789

4 Hour

Beta

Johnson and Co

Jane Doe

1/1/2013 10:14

555123

24 Hour

Theta

Civic Express

Bob Builder

1/1/2013 10:12

555678

24 Hour

Alpha

General Store

John Smith

My question is, how can I use google sheets to calculate the following information automatically, based on date ranges entered?

1. Number of Work orders submitted by team, like a pie chart showing what percent of total volume came from each team, and a table showing how many of each service level were submitted by team 
2. Number of work orders submitted each day total, probably a bar chart showing number per day, and then a table showing how many of each kind per day. *Note: This will only apply to Friday, Saturday, and Sunday, which are our "After hours" operations. 
3. Number of work orders submitted by customer, like a pie chart totaling up all the work orders submitted by all customers, showing what portion of total volume came in from each customer.

What I have so far is on a separate sheet. The tables looks like this. 

Breakdown of Work Orders by Service Level per Day
	Friday	Saturday	Sunday	Monday	Subtotals	Total
4 Hour					0
24 Hour					0
2 to 5 Day					0
Subtotals	0	0	0	0
Total						0

Breakdown of Service Calls by Team
	4 Hour	24 Hour	2 to 5 Day	Subtotal	Total
Alpha				0
Beta				0
Gamma				0
Theta				0
Other				0
Subtotals	0	0	0
Total					0

If I could get those tables to calculate automatically, that would be awesome. I could probably figure out how to get the charts to work myself.

I realize that this is a huge project/question, and if you would prefer to solve it over chat or video or whatever, let me know. I would most definitely be willing to do that.

Solution:

Instead of having data in separate columns for each team.<<Each team gets its own page, because each team has different project coordinators and different customers.>>
You can have data in the same columns, as you can differentiate it by Column "service team" that is Column D.

Have a look at the following screenshot of my Spreadsheet:

Sheet 'Data':

Sheet 1:

I have the following formula in Cell A1:

=query('Data'!A:F;"select D,count(B) where D<>'' group by D ")

Sheet 2:

I have the following formula in Cell A1:

=query(arrayformula(if('Data'!C:C="";"";iferror(if({1,0,0};choose(weekday('Data'!A:A);"01 Sunday";"02 Monday";"03 Tuesday";"04 Wednesday";"05 Thursday";"06 Friday";"07 Saturday");if({0,1,0};'Data'!C:C;1)))));"select Col2,count(Col3) where Col1<>'' group by Col2 pivot Col1")

Sheet 3:

I have the following formula in Cell A1:

=query('Data'!A:F;"select D,count(E) where D<>'' group by D pivot C")

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 and confidential projects:

http://igoogledrive.blogspot.com/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