Tuesday, April 26, 2016

yogi_Assign Numeric Value To Text Attributes And Sum Up Row By Row Score From Different Sheets

Google Spreadsheet   Post  #2062
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-26-2016
question by: Tanya Peters:
Data Validation - Assign Value for text
I am needing to assign a value to some text that we are using to score a person from 1 to 3 for our application process. Can anyone help on how this should be done as we have 3 sheets we are compiling into a main dashboard to pick our top candidates.

I also would like to have the candidates on the main page and have them compile into each of the 3 sheets so we are always the same across the board.

Here is a link to the spreadsheet we are working on.

Hope this makes sense.

Monday, April 25, 2016

yogi_From Data In 'Form responses' Sheet Compute Stats By Day_Of_Week Week_To_Date Month_To_Date Year_To_Date

Google Spreadsheet   Post  #2061
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-25-2016
question by: zohair92:

Form Responses Sheet (From a Google Form) ->Organizing Into Periodical Tabs (Weekly...etc)

 Hello all, 

I have a Google Form which tracks three kinds of warranties sold by my employees. 

Naturally, the responses to the form go to a Google Doc Sheet. The Form tracks the following information: The date/time, the employee name, and the warranty sold. What I would like to do is have a tab for Daily results (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday), a tab for Week to Date Results, a tab for Month to Date Results, and a Tab for Year to Date results.  

Essentially, I'm wondering if there is a way to automatically filter the raw data by using a calendar format? So I would like the current week to be displayed in the Daily Results Tabs.  In the Week To Date tab, I would like it to tally up the results for the week based on the day of the week (so if its wednesday, tally up monday+tuesday+wednesday). Sort of a live tracking system. 

I know it sounds complicated, so I've uploaded a sheet as an example of what I want to see. 

Please ive been struggling with this. If you need more information please let me know!

Thank you!!



Sunday, April 24, 2016

yogi_Custom Sort Data In Columns E2:H To match The Order Of Entries In Columns A2:A

Google Spreadsheet   Post  #2060
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-24-2016
question by: sc456a:

Matching two sets of data by columns

Here's an example:

What I'd like to do is sort the right four columns to match the order found in the left four, based on the keywords matching. Word1 would then be at the top, followed by word2, etc, with the data in the far right three columns resorted as well, for an end result like so:


Any help would be appreciated!

Sunday, April 10, 2016

yogi_For The List Of Descriptions Used In Cells A3 to A Sum Up Number Of Days For Which Booking Is Made

Google Spreadsheet   Post  #2059
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-10-2016
question by: Cara Klein:

Data Validation

Hello, like several people, I can't get data validation to work. 

This is what I want to do:
I have a cell, where the user should select from a list of descriptions. 
IF, description A, is selected, then the numerical value 1 should be displayed in the cell. 
At the bottom of the column, the total score based on numerical values will be calculated. I have followed everyone's instructions, and I just can't get it to work. The thing is, I can't have two separate cells: the numerical value must appear in the cell where the descriptor was selected from a drop down menu. 

Can somebody explain it to me (as if I was a 95 year old grandma??)

Friday, April 8, 2016

yogi_Pull From Sheet1 Only First Instance Of Records In Columns A to D

Google Spreadsheet   Post  #2058
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-08-2016
question by: arianne2013new:
Unique Value
Can anyone help me to find a unique value for Reference Number.  I want to remove duplicates of the REference numbers

Here is the link


Thursday, April 7, 2016

yogi_Compute Hours By Subject From Table Of Date Subject Time_IN And Time_Out

Google Spreadsheet   Post  #2057
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-07-2016
comment by: David Brinkley:
in my following blog post:

yogi_Computation Involving Multiple And Or Conditions

I have a similar question so I hope you are still monitoring this old post.

I am making a log for my son to use to record his daily activity in his online school
Here is the situation...
the data looks like this
Date                  | Subject            | Time IN     | Time Out   | Time Total   |
04/07/2016     | English            | 7:00 AM    | 8:00 AM     | 1:00              |
04/07/2016     | Math                | 8:00 AM    | 9:00 AM     | 1:00              |
04/07/2016     | Science           | 10:00 AM  | 11:00 AM   | 1:00              |
04/07/2016     | Math                | 11:00 AM  | 12:00 A M  | 1:00              |
04/07/2016     | Economics     | 12:00 AM  | 1:00 AM     | 1:00              |
04/07/2016     | Spanish           | 1:00 AM    | 2:00 AM     | 1:00              |
04/07/2016     | History            | 2:00 AM    | 3:00 AM     | 1:00              |
04/07/2016     | English            | 3:00 AM    | 4:00 AM     | 1:00              |
04/07/2016     | History            | 4:00 AM    | 5:00 AM     | 1:00              |

As you can see, he works on some subjects several times a day. I need a function that will check lines Sheet2!A2:A1001 to see if any of them match the date for the current line (Sheet1!A2) and if the date matches then it needs to compare the "Subject" to see if it matches, lets say, "Math" and if the date and subject both match then it will add the Total time to a running total for that subject on that date.

So it looks at
Line 1, date match, subject no match
Line 2 date match, subject match so +1 Math=1
Line 3 date match, subject no match
Line 4 date match, subject match so +1 Math=2
Line 5 date match, subject no match
Line 6 date match, subject no match
Line 7 date match, subject no match
Line 8 date match, subject no match
Line 9 date match, subject no match

Date              |    Math    |    Science    |    English    |    Spanish    |     Economics    |    History    |
4-7-2016      |       2        |           2         |            1       |         1           |         1                  |         2         |

Like so. So anywhere on sheet 2 column A that the date 4-7-2016 appears it needs to then check the subject and add it to the correct column for that date on sheet 1.

Rather then writing the formula for me I would love to just understand the syntax. Don't get me wrong lol you can write it for me but also explain it to me.

Monday, April 4, 2016

yogi_In Form Responses Sheet Separate Multiple Entries In A Cell Into Different Rows

Google Spreadsheet   Post  #2056
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-04-2016
post by: myxlphlyx:

Separate cell data into different rows

I have a Google form where users are entering multiple names separated by a comma (e.g. Joe Smith, Jane, Doe, John Doe). I would like to separate the names into 3 separate rows if possible (e.g. Row 1: Joe Smith. Row 2: Jane Doe. Rown 3: John Doe). Does anyone know how I can do this with a formula or add on?

I've seen add-ons that can separate first/last names into separate columns, but I need a new row for each name.

Thanks in advance for your help!

Sunday, April 3, 2016

yogi_Sum Up Hours For Days From Sheets Named By Month And Day Number Of the Month

Google Spreadsheet   Post  #2055
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-03-2016
post by: arianne2013new:

total hours for the week and per day

 Can you guys help me how to get the total hours for the week and per day for each names