joconnell19
New Member
- Joined
- Jun 17, 2021
- Messages
- 1
- Office Version
- 365
- Platform
- Windows
Hi all
I'm creating a questionnaire where we need to allocate values to dropdown options.
For example, I've got a list in a dropdown (I've used Data Validation to set up the list). The column on the left, are the options in the dropdown. The values on the right are the values we need to allocate to the dropdown options. So if <1% is chosen from the dropdown, the value cell with autopopulate with the corresponding value 0.
The questionnaire looks as follows. You can see on the second row what we want and we want the value to autopopulate based on the list above.
The third row is an example of what it will look like - as per my example above, you can see that if "Question is not answered." is chosen, then the value 0 is autopopulated in the right-hand column.
I have over 110 different lists different corresponding values, so if there is an easy way to have the value cells relate to the answer dropdowns.
One option could be to put in multiple nested IF formulae, but I'd like to avoid that as we may change the dropdown options in the lists so we need it to be something that someone can edit just by changing the text in the answer options or changing the values.
I know that another option would be to put in a form control and have all of the dropdown answers appear in the one cell, but have a form control tick box that then relates to the value cell, but we want all the lists to have dropdowns if possible as it looks tidier on our document.
I'm creating a questionnaire where we need to allocate values to dropdown options.
For example, I've got a list in a dropdown (I've used Data Validation to set up the list). The column on the left, are the options in the dropdown. The values on the right are the values we need to allocate to the dropdown options. So if <1% is chosen from the dropdown, the value cell with autopopulate with the corresponding value 0.
The questionnaire looks as follows. You can see on the second row what we want and we want the value to autopopulate based on the list above.
The third row is an example of what it will look like - as per my example above, you can see that if "Question is not answered." is chosen, then the value 0 is autopopulated in the right-hand column.
I have over 110 different lists different corresponding values, so if there is an easy way to have the value cells relate to the answer dropdowns.
One option could be to put in multiple nested IF formulae, but I'd like to avoid that as we may change the dropdown options in the lists so we need it to be something that someone can edit just by changing the text in the answer options or changing the values.
I know that another option would be to put in a form control and have all of the dropdown answers appear in the one cell, but have a form control tick box that then relates to the value cell, but we want all the lists to have dropdowns if possible as it looks tidier on our document.
