yogi_Compute Sum Of Hours By Day, Month, And Year As Specified Year

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

AFUSA said:

Need formula to interpret timestamp's from Form, by month, year and by day,month,year
I have a Form that employees submit their time worked each day. The time stamp logs it. I need a formula to add all the hours of this month, from this year, and for this day, this month, this year.
Here is what I was using: =ArrayFormula(if(len(J1),SUMIF(month(A2:A),J1,C2:C),iferror(1/0))
But next year it will include the months from both years.
------------------------------------------------------------------
In the following solution I used the Query function to compute the sum of hours for current day, current month and year, and current month all years

yogi_Paste Value In A Cell Using Data Validation

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
jkm1317 said:
Is there a way to automatically input a formula for ONE TIME only? I have a sheet where I am using vlookup to calculate a total. (Price*quantity = total). Well as time (years) goes on this price has changed (e.g. inflation). So my issue is that when i change the price all my old calculations would change as well. i would like gdocs to lookup the answer and hold it.
I hope you can help!
Thanks!
------------------------------------------------

yogi_Find Information For A Validated Cell From Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

chk4p said:

So let's assume that I have a sheet ("ContactInformation") of contact information that looks like this:
John Smith, email@email.com, 5133471111, etc
Jane White, contact@random.com, 4343141592,etc
Now, in a separate sheet, I have a row (we'll call it the "SelectedUser" row) that begins with a validated cell (drop down), from which I select John Smith:
How can I populate the rest of the SelectedUser row with the information from the "ContactInformation" sheet?
I want this to be done *dynamically*. For instance, specifying a long block of IF's is not useful, because it doesn't actually cut down on much work.
Any tips, tricks, ideas?
--------------------------------------------------------------

yogi_Compute Employee Hours Worked From Time_In Time_Out Clocked Via Google Form

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

jb2301 said:
I'm pulling my hair out trying to figure out how to get a formula to copy itself down when a form posts
I am using a Form as an employee timeclock. The three columns posted are timestamp, employee name, and Clock In/Clock Out. I'm trying to add some simple formulas to the right of the data that gets posted by the form to calculate my daily labor cost per employee. The problem is each time the form posts new data, my formulas aren't copied down to the next line where the new data was posted. I have to manually copy these formulas down each time. I've tried the Expand function and some other Array formulas but it hasn't worked for me. Can someone please help!!
----------------------------------------------------------

yogi_Extract Rows Of Interest From Sheet1 In Another Sheet And Publish Maitaining Security of Sheet1

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

klor000:
I'm sure this is very easy to do, but I just can't seem to figure out how to do it.
I have a spreadsheet that only I can read/edit. I want to extract out those entire rows in which the first character in column J is a 1 (entered as text, not a number) and put all those rows in another spreadsheet that I can then share with people who just need to see those rows of information. Of course, over time, when I change my original private spreadsheet, I want their extracts to be appropriately updated to show whichever rows currently have that 1 in column J.
Thanks a lot for any help. I'm obviously quite new to this.
Karen
-------------------------------------------------------------------
Hi Karen:
In Sheet2 I have extracted the rows of interest per your specification, and then I have published only Sheet2

yogi_Extract Rows Of Interest From Sheet1 In Another Sheet And Publish Maitaining Security of Sheet1

klor000:
I'm sure this is very easy to do, but I just can't seem to figure out how to do it.
I have a spreadsheet that only I can read/edit. I want to extract out those entire rows in which the first character in column J is a 1 (entered as text, not a number) and put all those rows in another spreadsheet that I can then share with people who just need to see those rows of information. Of course, over time, when I change my original private spreadsheet, I want their extracts to be appropriately updated to show whichever rows currently have that 1 in column J.
Thanks a lot for any help. I'm obviously quite new to this.
Karen
-------------------------------------------------------------------
Hi Karen:
In Sheet2 I have extracted the rows of interest per your specification, and then I have published only Sheet2

yogi_Extract Entries That Do Not Match Entries In A Specified Column In Another Sheet

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com
fin.severven said:
The problem:
I want sheet 3 to have lines from sheet 1, that has only those letters in Sheet1!B:B that DO NOT match with letters in Sheet2!A:A.
I guess it could be some combination of Filter and Unique, but could not find the way to do it.
For example:
sheet 1
A B
1 timestamp letter
2 01/01/00 00:00:01 a
3 01/02/00 00:00:01 b
4 01/03/00 00:00:01 c
5 01/03/00 00:00:02 d
Sheet 2
A
1 a
2 b
3 e
Sheet 3 should be
A B
1 01/03/00 00:00:01 c
2 01/03/00 00:00:02 d
Thanks in advance for any help with that.
---------------------------------------------------------