Tuesday, June 25, 2013

yogi_Search Sheets '1st' '2nd' etc. And Pull Information By Last Name Of Student

                                          Google Spreadsheet   Post  #1263
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 26, 2013
user Heath Luehmann (http://productforums.google.com/forum/?zx=81uncl5s0y25#!category-topic/docs/spreadsheets/TVL6-nc_RcM)
How to search a spreadsheet and return values of the row
I have a database that holds student names, groups, leaders, and schedule on nine sheets.  I would love to be able to do a search over the entire database for something like "Veronica" which would return something like this:

Name: Veronica Smith
Group: 2 - C
Leader: Carol Johnson
9:00: Room 212
9:30: Room 301
10:00: Gym
10:30 Room 233
11:00 Snacks

And would also be able to list several options if there were more 'Veronicas' in the list.

I've been looking through formulas and scripts, but I have not found something that would work for this, yet.  Any input would be appreciated!  Thanks.
---
Thanks for being willing to help, Yogi.

a) Our church does a vacation Bible school that has almost a thousand kids that we need to keep track of.  We found that a google spreadsheet is so much better than emailing new spreadsheets every time there is a change.  We are also able to allow others to have viewing access of the document for different areas of the campus.  Much of this is done on phones (android and ios).  Finding the information is not always easy on the mobile devices, so I was considering a formula that would allow a search in a cell resulting with matches.  I also considered a script that would create a pop-up box with the information.  We would want to be able to search:

By a kid's name and get the crew, crew leader(s), and schedule
By a crew and get the kid, crew leader(s), and schedule
By a crew leader and get the kid, (other crew leader, if applicable), and schedule

Most of the examples I was looking at online were for very small amounts of specific data.  I would want to be able to search of any of these and get all options quickly.

b) The cell does not matter.  Maybe it would be best to put it in a separate sheet which could be embedded within a separate google site that would work well for mobile devices and display results.

c) I changed the data from this spreadsheet from a past year, but it would work for an example.  If I searched for Jacob, I would want to see:

Last Name: David
First Name: Jacob
Crew: K - F
Crew Leader: Marissa Gianino,
Lexie Schomaker
9:00: SW169
9:35: Sanctuary
10:00: Chadder
10:20 Snack
10:40 Bible
11:00 Games
11:20 Crafts

Last Name: Minton
First Name: Jacob
Crew: K - U
Crew Leader: Madeline Kaempfe,
Audrey Wind
9:00: SW213
9:35: Sanctuary
10:00: Chadder
10:20 Snack
10:40 Bible
11:00 Games
11:20 Crafts


Let me know what you think.  Thanks.
-Heath
-----------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Total Price For Quantity Ordered Based On Multi-Tier Pricing System

                                          Google Spreadsheet   Post  #1262
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 26, 2013
user Cooper Brown (http://productforums.google.com/forum/?zx=81uncl5s0y25#!category-topic/docs/spreadsheets/mT06SpkrTLw)
Challenging sum problem
Can anyone help me with the following summation formula:  $145 for 1 unit, $165 for 2 units, $195 for 3 units, and $45 for every unit over 3.  I would like to be able to enter the number of units into a cell and have dollar total show up in the cell directly to the right.  Is this at all possible?   Thank you very much for your help.
-----------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Query Data based On Header Names in Row 1 Of Summary Sheet

                                          Google Spreadsheet   Post  #1261
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 25, 2013
user Tor Cunningham (http://productforums.google.com/forum/?zx=ksirz5o3le8l#!category-topic/docs/spreadsheets/NRDcG9PYcCw)
Query data based on header names
I have a large array of data in one sheet which I want to summarize in a second sheet.

I need a formula to bring across data depending what header is in the first row.

So in the example sheet linked below I would like to insert a formula in Summary!A2 that will bring through data for all three columns that have the same header in the corresponding data sheet no matter what order they are in.

I have previously just used a formula in each column but now have many data sheets that have header names in different places and it makes merging the data difficult.

sample sheet link:


thanks in advance for any help

Tor
--------------------------------------------------------------------------------------------------------------------------------------------------

Sunday, June 23, 2013

yogi-Create Multi-Condition Computed Column In 'Form Responses' Sheet

                                          Google Spreadsheet   Post  #1260
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 23, 2013
user E Paul (http://productforums.google.com/forum/?zx=c8plvrplz67y#!category-topic/docs/spreadsheets/X5NgMOp--M4)
Formula and data from forms
So I created a form that feeds to my sheet - on the sheet I created a new column that contains a formula - the formula is a simple if/then statement that is supposed to change the color of that cell when data is entered into other cells (basically a status update).  I copied the formulate and that seems to work if I enter/change information on the sheet directly.  However, If I use the form to enter data it seems to just delete the formula.  

Any ideas?
----------------------------------------------------------------------------------------------------------

yogi_ Determine Availability Of Staff By Name From Data In Project Management Info Table

                                          Google Spreadsheet   Post  #1259
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 23, 2013
user CK Vijay Bhaskar (http://productforums.google.com/forum/?zx=c8plvrplz67y#!category-topic/docs/spreadsheets/DbKsYuAhek0)
Multiple Condition Issue
Hi,
This is my problem statement - 
To check if a person is free between a given set of date range. 
I tried searching in the group, and I incorporated some of the suggestions based which I got the solution for Excel, but not for Google Spreadsheet. 
I have a task sheet with multiple set of tasks assigned to various people. Each row as the task name/details, person responsible, plan/actual start and end date.
I am trying to determine the availability of the person based on the plan start/end date across the tasks.
I was successfully able to do this in Excel using this formula - 
=IF(SUMPRODUCT((Data!D:D=$C$1)*(A2>=Data!I:I)*(A2<=Data!J:J))>0,"No","Yes")
Where -
Data!D:D has the range containing all the people
$C$1 - is the name against which I am searching
A2 = is the date which I am checking if it is falling between two date ranges. The A column contains the days within each month against which I am checking if the person is available or not.
Data!I:I = contains the range of plan start date
Data!J:J = contains the range of plan end date
But when I tried to do this in Google Spreadsheet, It is not giving me the the expected result. The formula I have used is this -
=IF(SUMPRODUCT(('Data'!$G:$G=$B$4)*(C$2>='Data'!$L:$L)*(C$2<='Data'!$M:$M))>0,"No","Yes"), where - Data!$G$G = name range
$B$4 = is the name against which I am searching
C$2 = date row
Data!$L:$L = Plan start Date range
Data!$M:$M = Plan end Date range
Any help would be great. Thanks in advance for your time. 
---
I was taking time to remove unwanted data.

As you will see, I need to track against the date if a person has planned a task or not so that I can see if the person is available or not for a particular date.

Hope this helps. 
Please do let me know if you would need some more info to arrive at the correct solution. 
-------------------------------------------------------------------------------------------------------------------------------------------------------

Saturday, June 22, 2013

yogi_Pull From 'Master Data' Names Of Camp Participants By Weekday For The Specified Week

                                          Google Spreadsheet   Post  #1258
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 22, 2013
user Meaghan Lister (http://productforums.google.com/forum/?zx=71dqfph2fqpy#!category-topic/docs/spreadsheets/EqBmVGVF3_c)

I am trying to sort / filter data to create a registration list from a master list.

I have a master list that contains names of registrations, what days of the week they are attending and what weeks they are registered for.

ie. Columns include:

Name
Monday
Tuesday
Wednesday
Thursday
Friday
June 26 - 28
July 2 - 6 etc.

I think that I can use the filter function, but I'm not sure how. I want to be able to create a sheet that will filter the data so that I know who is coming on Monday, Tuesday, Wed etc, of each week of the Summer Camp program.

Any ideas?
---
--------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute For Each Month Of Dates in ColumnA (Sum of AmountB) - (Sum Of AmountC)

                                          Google Spreadsheet   Post  #1258
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 22, 2013
user Jarorosor (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/zV2UVY_ZHhY)
How to sum between to specific dates 
Hi!

I´m trying to calculate values from two columns within each month.

For example

A                         B               C          Months
--------------------------------------------------------------------
2013-06-01          150                          June: 240
2013-06-05          275                          July: 45
2013-06-27                          185          August: -10
2013-07-01                          75
2013-07-20         120
2013-08-01         50
2013-08-18                         25
2013-08-25                         35

So what i'm trying to do is sum column B minus column C for each month with a formula and I can't get it right using SUMif.
Anyone who can help?

Thanks in advance.
----------------------------------------------------------------------------------------------------------------------

Trial_yogi_Create A Spreadsheet Publish It In ListView Then GoTo Spreadsheet View For OnLine Edit


Friday, June 21, 2013

yogi_Query Data From A Table For Specific Values In Columns Including The Option of Choosing All Values In Specified Columns

                                          Google Spreadsheet   Post  #1257
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 21, 2013
user Ruan Davel (http://productforums.google.com/forum/?zx=whyulvkvwedi#!category-topic/docs/spreadsheets/Uw5ITuWe53w)

Hey Folks,


What I am trying to achieve is that you can search/filter the data coming in from the form according to -
Name
Kitchen
Time
and Between 2 dates.



This is the formula - 
=if(and(B5="All";D5="All";F5="All");query('Form Responses 1'!A:K;"select *
where toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(B5="All";query('Form Responses 1'!A:K;"select * where C = '" &D5& "' and B = '" &F5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(D5="All";query('Form Responses 1'!A:K;"select * where B = '" &F5& "' and D = '" &F5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");if(F5="All";query('Form Responses 1'!A:K;"select * where D = '" &B5& "' and C = ‘”$D5$”’ and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'");query('Form Responses 1'!A:K;"select * where B = '" &F5& "' and C = '" &D5& "' and D = '" &B5& "' and toDate(A) >= date '" & text(H5,"yyyy-MM-dd") &"' and toDate(A) <= date '" & text(H6,"yyyy-MM-dd") &"'")))))
The problem is that when I filter/search according to a specific name in B5 and D5 & F5 = All then I doesn't give the result.
So obviously in the formula I am using I have missed something....completely....
If someone knows what is needed please let me know.
---
I guess the best way to say what I want to do is this.
The data in B5, D5 and F5 (as selected in the drop down lists) should correspond with the rows of data from where I am getting it from in the form responses sheet.
But when I select "All" in B5, D5 or F5 (or when only one of them) then all the corresponding rows from the dorm responses sheet should come up.

Hope that makes more sense....
-------------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Score For Individual Performance Based on Specified Criterion

                                          Google Spreadsheet   Post  #1256
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 21, 2013
user aidacuk (http://productforums.google.com/forum/?zx=whyulvkvwedi#!category-topic/docs/spreadsheets/wJshkU062jg)
Formula Issue With Form Submissions
Hi,


I have posted this in the form section however have had no luck.

I've created a form to establish Yes or No answers based on an agents performance.  For every No received this removes a percentage from 100%.  Using the formula is fine however every time a new response is submitted the formula I have put in jumps down another row.

I have seen many people suggest ArrayFormula but that hasn't worked.  Maybe I'm doing it wrong?

The formula I have entered is:

=Arrayformula(100-((COUNTIF(C2:AY2,"No"))*2.38))

I have also deleted all empty rows, but still this doesn't copy the formula down when a new response is received.

Thanks.
---
Hi Yogi,

This is the spreadsheet:


Thanks,
-----------------------------------------------------------------------------------------------------------------------------------------------

Thursday, June 20, 2013

yogi_Create A Cross Tab of Status 'Attribute' By Name And Chart The Results

                                          Google Spreadsheet   Post  #1255
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 20, 2013
user Sim S (http://productforums.google.com/forum/?zx=1un23xjfx15h#!category-topic/docs/spreadsheets/4q9DqWOkk1U)
google spreadsheet formula help
im trying to make a chart with the results below is link for the test spreadsheet

so as you could see spreadsheet has names and status
what i'm trying to do is add the total numbers for each persons name with it's status
example: 
james closed
james closed

so results column under closed will have right next james with number 2  
column "closed" number 2
column "hold" number with 0
column "open" number 1

thank you in advanced
---------------------------------------------------------------------------------------------------------------------------------------------

yogi_Compute Row By Row Average Number Of Each Brand in Column A

                                          Google Spreadsheet   Post  #1254
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 20, 2013
user Gregg Manson (http://productforums.google.com/forum/?zx=1un23xjfx15h#!category-topic/docs/spreadsheets/RjG5O1dQ80U)
Average with multiple criteria
Help - attempting to average a column by brand, exapmle below

  A         B
Audi     265
Audi     125
BMW   298
BMW   890


Would like to return in a cell the average for each Brand.  I have 20 different Brands and apx 100 rows of data.

Really appreciate the assistance.
-------------------------------------------------------------------------------------------------------------------------------------------------

yogi_Trim Out Strings For Spurious Space Characters For Correct result using COUNTIF

                                          Google Spreadsheet   Post  #1253
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 20, 2013
user Daniel Nice (http://productforums.google.com/forum/?zx=o5tj0hu108x2#!category-topic/docs/spreadsheets/yZoe_HwqOv0)
Need help counting values within array
Can someone please explain to me why the top countif works as expected and the bottom one does not?

https://docs.google.com/spreadsheet/ccc?key=0AjK4ZXNh_OmpdHJhdjN2SjhTOUhKSkN0UVBnNjJ1d0E&usp=sharing
Me, too!
----------------------------------------------------------------------------------------------------------------------------------------------------