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.
Michael Mathews is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
2
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)))
Explanation:
'Form Responses 1'!A2:Mis assigned an identifierΛvia let
functionoffset(Λ,-1,,1)translates to'Form Responses 1'!A1:M1& is also
assigned another identifier- the whole choosecols() part loosely translates to
choosecols('Form Responses 1'!A2:M,1,8,4,12)for Vicky xmatch(B4&" ["&A2&"]",Σ)translates toxmatch("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. finallychoosecols()arranges them all as one output
4