google-sheetsgoogle-sheets-formula

Google Sheets Query referencing a Dropdown selection


I am attempting to create a sheet that populates data based upon a drop down menu. The data is coming from a Google form and has the following headers:

Timestamp Decoding John Decoding Anna Decoding Vicky Decoding Brad Encoding John Encoding Anna Encoding Vicky Encoding Brad Write a dictated sentence John
Write a dictated sentence Anna
Write a dictated sentence Vicky Write a dictated sentence Brad

What I am trying to do is have a drop down that has these names as options. When a name is selected, the main page populates all data that is in the columns with the corresponding name and the time stamp.

I've tried several different methods of doing this but they've all failed. Can someone assist with this?

Here is the copy of the sheet:

https://docs.google.com/spreadsheets/d/1GdF9-iMhiDA3AiL4CkqRM8ByWEawTS0fVh85DNo9Qbw/edit?usp=sharing

I've tried using QUERY and IF statements but I'm not sure what to actually use. The Query option sounds like the correct one but I can't reference the drop down since they're on separate sheets.


Solution

  • Here's one approach you may test out:

    =let(Λ,'Form Responses 1'!A2:M, Σ,offset(Λ,-1,,1),
     choosecols(Λ,1,xmatch(B4&"  ["&A2&"]",Σ),xmatch(C4&"  ["&A2&"]",Σ),xmatch("*sentence  ["&A2&"]",Σ,2)))
    

    enter image description here

    Explanation: