Updating Cells in Google Sheets by latest Timestamp input from Google Forms

user_15210146 0
Viewed 122

My Problem:

I would like to make updating cells in google sheets based on the last input from a google form. Does somebody know a solution?

What I tried:

I made a query function that takes the form inputs and added a row next to the data that counts every input(=IF(B2="";"";1)than in the cell below(=IF(B3="";"";A2+1), so each new input has a higher value than the one bevor. Then I tried to write a function for a fixed cell that queries the inputs of the list in search for the highest value, but I can´t find a formula that worked.

Example:

Exampe Data

google-forms google-sheets
1 Answers

You can do this by query()

Formula:

=ifna(query('Form Responses 1'!$A$2:$C,"select C where B = "&indirect(address(row(), 1, 4))&" order by A desc limit 1"), "")

Note:

  • Just copy the formula directly to the status column on sheets tab, no need to edit per cell
  • It gets the last value of column C where column B is equal to the ID number of the row. Returns blank if ID number is not present.

Sample Data:

sample

Output:

output

user_161186860
Related