Formulas for autofilling an entry dependent on the previous question

[Erel]

Hi

I am trying to find the formula for a column when:

Q2 automatically enters an answer dependent on what was selected for Q1 from the preset drop down menu.

So if Q1 is entered as the first choice then Q2 is autofilled with an answer etc.

Am on the right path with the IF Formulas?

[Bahbus]

There are a couple of ways you could do it. Depending on the number of options for Q1 you could use an IF, IFS, or even a SWITCH function.

[Jeremy_F]

Apart from IF, IFS and Switch, you can even use de-reference to auto fill Q2.

It would have been better to give a case scenario, then you can be advised on the right approach

[Steve]

Whatever expression you choose, it’ll probably best go in the Initial value property for Q2.

[Erel]

Thanks for the replies.

Q1 has over 90 options from a dropdown menu.

Q2 As a result from the selected answer from above, there are 5 options it can be.

Would someone be able to write out a formula that would work for that, I’m struggling to get it right?

[Lynn]

Hi @Erel I think we would need a bit more info to be able to supply a formula. What have you tried? Have you checked out

help.appsheet.com

Dependent Dropdown

How to make a dropdown with choices that depend on the value selected in another dropdown.

[Steve]

I imagine something like this might work for you:

IFS(
  IN([q1], LIST(q1-opt1, q1-opt2, ...)),
    q2-opt1,
  IN([q1], LIST(q1-opt7, q1-opt8, ...)),
    q2-opt2,
  ...
)

[Jeremy_F]

I would create another table for holding the options value (call it OptionTable), with columns Question 1 and Question 2. - Question 1 column will have the 90 options, and Question 2 column will have one of the 5 options you require

Then, on Q1 column for the main table,
1 - set the column type as ref and source table is the OptionTable
2 - set valid if to use the formula Select(OptionTable[Question 1] , True) - To retrieve all the 90 options

Next, on Q2 column for the same main table, set initial value as [Q1].[Question 2] - This pulls question 2 value from the OptionTable

N.B To understand deference, check out this link https://help.appsheet.com/en/articles/1090811-dereference-expressions

[Mark_11]

i wouldn’t use a formula with a lot of fields;
is defenetefly a easiest solution and more sustainable if u need to change it in the future, a dedicate table with two column, like @Jeremy_F told you.

Q1 | Q2 |
brown | colour
yellow | colour
red | colour
cat | animal
dog | animal
bird | animal
.
.
With this method you can easily update your list in the future if you will need to. You can also add other coloumn with other sub-categories if you need. Which coloumn is “Q1” or “Q2” is your choice.