Tuesday, July 1, 2014

yogi_Pull Last Action (clock out or clock in) From Data In 'Form Responses' Sheet

                                       Google Spreadsheet   Post  #1686
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jul-01-2014
post by Ed Burch: (https://productforums.google.com/forum/#!mydiscussions/docs/aDVporB9ez8)
Find last entry of a user
I have a 'Form Responses' sheet that I use as a datasource. From a different sheet I have the user select a username from a dropdown. I then would like the last timestamp entry on Form Responses to populate according to the dropdown name. CAn someone help with this code.


The formula I have tried is 

=query(Form Responses!A2:A;"select max(A),Where Col3 =B2")

this is in cell b3
---
Sorry Yogi for not being clear.
On sheet Last Recorded Entry in cell C3 I have the following code

=QUERY('Form Responses'!A2:I2000, "select I where A = """&B3&"""";1)

this formula should query "Form Responses" column I where column A of "Form Responses" is equal to column B3 of "Last Recorded Entry" and return the result either "clock in" or "clock out" depending on the entry in column I of "Form Responses". Instead it returns the next entry down from column I regardless and this is not what I was trying to accomplish.

in the above example the expected result would have been 'clock out'.

This formula should also continue down the rows for each entry.
-------------------------------------------------------------------------------------------------------------------------------------


No comments:

Post a Comment