Google-sheets – Populating alternatives in question 2 based on answers in question 1

data validationformulasgoogle sheetsgoogle-formsgoogle-sheets-arrayformula

I have ten people who sell something to companies, and I want to track what they do IN GOOGLE FORMS. Every agent has his own 10 companies that he contacts.

So, in Question 1, I will have a drop-down question where an agent will identify himself from the list of possible names.

After that, based on his answer on Q1, I want to have Question 2 where he will have a multiple choice question where he will identify one of his own 10 companies, and proceed to the next part. Q2 alternatives for each are dynamically populated via Google script from a Google sheet column.

I do not want to point towards different "sections" in Google Forms based on answers to Q1, since they will generate separate columns in the answer sheet.
Is there any way that I could populate Q2 alternatives based on what was answered in Q1?

In other words, is there any way to look up the list of choices in Google Forms (not sheets!) on a question dynamically based on the prior answer?

Best Answer

formula in D2:

=IF(A2=F2, {H3:H},
 IF(A2=F3, {I3:I},
 IF(A2=F4, {J3:J}, )))

data validation in A2:

data validation in B2:

demo spreadsheet

Related Topic