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.
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)))
'Form Responses 1'!A2:M
is assigned an identifier Λ
via let
functionoffset(Λ,-1,,1)
translates to 'Form Responses 1'!A1:M1
& is also
assigned another identifierchoosecols('Form Responses 1'!A2:M,1,8,4,12)
for Vickyxmatch(B4&" ["&A2&"]",Σ)
translates to xmatch("Encoding [Vicky]",'Form Responses 1'!A1:M1)
which outputs 8 since it is found at the 8th spot in the header; the same goes for other 2 xmatches as well. finally choosecols()
arranges them all as one output