yogi_From A Table Of Fruits Associated With A List Of Names Pull Row By Row The Favorite Fruits By Name Of People In List

                                           Google Spreadsheet   Post  #1940
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-10, 2015
question by Andy2570:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/MjM8G2M5EOI
Find value in a list and then return the column heading
Hi there,
I have a spreadsheet that I've been working on for a while and am pretty stuck.

What I have is 2 sheets, one called "Data" which has columns of people's names based on different categories. The other sheet "List" has a list of everyone's name and I would like to find the names of anyone from the Data sheet, look at the top of the column to see the category their name is in and insert the headings next to their names. So Michael would have "Potato" and "Celery" next to his name (either together in the same field or seperate fields).

I've got an example sheet here if that helps:
https://docs.google.com/spreadsheet/ccc?key=0AiXms5FXF5MxdDc3U0J1TEFkeHpTNU9hbFAyVFlIS1E&usp=sharing

I've tried INDEX and MATCH but all I get is an error about range must be 1xN. I've also tried FILTER and HLOOKUP and that didn't work either. Not sure what I'm doing wrong.

Any help would be very appreciated!

Andy
-------------------------------------------------------------------------------------------------------------------------------------

yogi_Conditionally Format Cells Of Column A That House The Specified WeekDay (cell B5) Of The Current Week

                                           Google Spreadsheet   Post  #1939
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-08, 2015
question by Petr Pomahac:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/IkFJtm39X0o
How to conditionally format the date which represents Monday in current week - dynamically

Please help me with dynamic conditional formating. See the link below. Is it possible at all?

Thanks, Petr

https://docs.google.com/spreadsheets/d/1swY1E9BCuUowhDBnT24eGFt_3u820T8vjVyUIGppImk/edit#gid=0

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

yogi_Pull From Sheet Named 'Raw' First_Name And Last_Name Of Those People Who Have Run_Miles Done Push-Ups And Done Sit-Ups As Specified In Column C

                                           Google Spreadsheet   Post  #1938
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-05, 2015
question by Gerry Doyon:
https://productforums.google.com/forum/#!mydiscussions/docs/C5iTcddrrKk
Google Sheets: Need help with a SELECT statement

I have a Google Sheet that logs exercise data for people. Here are the columns:

Row B = First name

Row C = Last name

Row D = Miles run

Row F = Push ups

Row G = Sit ups

Each day, or multiple times during the day, people enter their data in the associated form. I created an spreadsheet that runs a number of different queries.

What I want to do now is display a list of all the people who have achieved a specific goal of:

Total miles run >= 100

Total push ups >= 1,000

Total sit ups >= 1,000

The following query showing their totals works:

Select B, C, Sum(D), Sum(E), Sum(F) GROUP by C,B LABEL B 'First Name', C      'Last Name', SUM(D) 'Total Miles', SUM(E) 'Total Push Ups', SUM(F) 'Total Sit Ups'`

Now I want to ONLY show people who have met or exceeded the goals listed above. I tried the following query, which doesn't work:

`Select B, C, Sum(D), Sum(E), Sum(F) WHERE Sum(D) >= 100 AND Sum(e) >=1000 AND Sum(F) >= 1000 GROUP by C,B LABEL B 'First Name', C 'Last Name', SUM(D) 'Total Miles', SUM(E) 'Total Push Ups', SUM(F) 'Total Sit Ups'`

Can anyone offer a suggestion that would make this work?

---

Hi all,

Yes, there are a few label types. Here is a duplicate copy of the spreadsheet:

https://docs.google.com/spreadsheets/d/1KEKN5-6FVCWLe41rzxfIWzyI8XIO8c-wiTlzSS2Y3ow/edit?usp=sharing

All of the data I sift through is in the "Raw Data" tab. All of my queries are in the "Queries" tab. 

If I just use a select statement that has something like "Where D>=100 OR E>=1000 OR F>=1000 " it will ONLY bring back the data entries that have met or exceeded this criteria. The problem is, there are only one or two people that recorded 100 miles, 1000 pushups and 1000 situps in ONE entry. there are probably ten people who, if you SUM all of their entries, will meet the criteria.

The problem is that Google doesn't seem to allow "SUM(C) AND SUM(D)" in the SELECT statement.

Row 44 of the Queries tab is where I am trying to get the SELECT statement correct. A copy lines down in the same tab, B46, is where I am testing the query. As you can see, only two entries are returned in the result, which is inaccurate.

Thanks!

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

yogi_Assign Weekly_Timestamp Instead Of Usual Timestamp To Full_Name And Number_Attended From 'Form Responses 1'

                                           Google Spreadsheet   Post  #1937
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-05, 2015
question by Gavin Lau:
https://productforums.google.com/forum/#!mydiscussions/docs/DZzydLJLfgQ
Is it possible to create a weekly time stamp rather than the normal mm-dd-yy time stamp?
Hey guy i have this application that i am currently working on which works as an attendance system. You can follow this link for more background information: https://productforums.google.com/forum/#!mydiscussions/docs/v1VGpiw_Frs (Thank you Scott for the huge amounts of help). 

Basically because this is a project i am working on, my supervisor is always giving me requirement changes So one of the features that i would like to implement now is to having a weekly stamp rather than the normal time stamp to show what week the attendance record is at, since i am going to be implementing an auto clear so that every week the sheet gets cleared automatically.

you can get more background information from the link provided above.

Kind Regards

Gavin 

---

Hi there Yogi, really sorry if my question was unclear. Just so you can get the bigger picture ive pasted my background which i had posted on another post last week:

Background:

my application that im building gathers connected device's IP address and MAC address as a unique way of representing a student. once the student devices connect to the hot spot the lecture with this application can then save this data into google sheets which will be updated live everytime he clicks the "Save Register" button.

Basically the first form (response form 1) will get updated everytime i press a button on my android application to save the register. The second form acts sort of like a one off register that a student will fill in when the form is sent to them to basically build a database of all the student numbers and mac addresses of their devices. I used this method as i could not implement any other way of getting student numbers with their mac address since im not that proficient with android programming. The 3rd form "Attendance Record" is what another member has helped me out on to create which counts up how many times a student has attended class.

====================================================================================================================

here is a copy of my spreadsheet, https://docs.google.com/spreadsheets/d/1HADXCwEaJV10dT9kGDfU8W7re5E0yoew1o8Cu185tFQ/edit?usp=sharing

a + b) i want to know how i can add a weekly time stamp for the "Attendance Report" sheet. So in cell A1 i would like something which displays the date or week as shown below in c)

c)Expected result = http://gyazo.com/e8a990da353ab08df1e17d7b077356f1

And from then im hoping to create an app script i believe its known as an event trigger or time trigger, please correct me if i am wrong. So that the spread sheet clears automatically each week so a new attendance record can be made. I know this may sound far fetched because im completely new to google sheets and how i manage to integrate it with android even i dont know how.

I want to thank you for taking the time to read my post it is really appreciated. If you could suggest any better solution on how i can achieve a the expected result im all ears.

Kind Regards

Gavin

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

yogi_Compute Row By Row If givenTime Falls Between startTime And endTime

                                           Google Spreadsheet   Post  #1936
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-05, 2015
question by Mark Agustin:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Mk2xMfM3s10
If time falls between two TIME (Crossing Midnight)

Hi,

Badly need help. I need to know if a certain time falls between two (2) TIME (that crosses midnight). It's goes like this:

B1: 23:00 (start)

C1: 8:00 (end)

D1: 7:00 (time given)

I want a result that says "YES" since 7:00 falls between the A1-B1. I made a nested if, but it doesnt seem to do the job:

(IF(D1="",0,IF(C1=D1,C1,IF(D1=0,C1,IF((C1>=D1)+(C1<=E1)+(E1<D1)=2,"YES","")))))

This results YES to all. Really need your help!

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

yogi_Compute Sum Of Prices Based On Selected Features From Data In Different Sheets

                                           Google Spreadsheet   Post  #1935
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Apr-04, 2015
question by joebaz:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/Yr3Em_CwmYY
Alternative to Filter function for displaying a list of selected cells
Hey Docs community, 

I've had some success outputting a set of cells based on a data validation toggle by using the FILTER function, but unfortunately the FILTER function doesn't give me the flexibility to manipulate the output. I want to know if there is an alternative way to display a set of cells based on the selection of inputs from a different sheet. The catch is that the output of rows cannot contain and blank rows in between. (i.e. If I choose A1 and C1, the output of those on the subsequent sheet show be rows A and B, not A and C.) 

It's probably better to see the sample spreadsheet I have created to demonstrate what I am seeking. 
https://docs.google.com/spreadsheets/d/1C4LTsspe1Jm7LpojanWuEfrIkfit_YFAf6dUdRVMMyg/edit#gid=0

Thanks in advance for your help!
--------------------------------------------------------------------------------------------------------------------

yogi_Compute Gestational Age In Weeks_And_Fractions_Of_Weeks Based On Due Delivery Dates

                                           Google Spreadsheet   Post  #1934
              Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 29, 2015
question by Eric Hawes:

https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/-arkdoM6cVg

Calculation of gestational age

I have a table with due dates in the format of mm/dd/yy in column b and would like to calculate gestational age in column c in the format of weeks and days gestation 

(i.e. 36 3/7 weeks). 

Can someone help me with a formula that I can apply to all of the cells in column C? 

Thank you.

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