Adding a value to dropdown lists

joconnell19

New Member
Joined
Jun 17, 2021
Messages
1
Office Version
  1. 365
Platform
  1. 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.
1623966391317.png


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.
1623966514469.png


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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
Hi JoConnell19,

In its simplest form you could just use a question/score matrix for the datalist and result. This example shows that approach for Q1 and Q2.

It would be better to Excel tables so you can use Named ranges which makes them easier to understand and allow you to auto extend the list of values without changing the formula, as I've done for Q3.

JoConnell19.xlsx
ABCDEFGHIJKLMN
1Q1A1Q2A2Q3A3
2Dog5Audi2Marmite4
3Q1AnswerDogCat1BMW1Cheese3
4Dropdown Options5Goat3Ford5Jam2
5Questions not answered Sheep2Toyota3Peanut Butter5
6Kia4
7Q2AnswerKia
8Dropdown Options4
9Questions not answered 
10
11Q3AnswerCheese
12Dropdown Options3
13Questions not answered 
Sheet1
Cell Formulas
RangeFormula
C4C4=IFERROR(INDEX(H2:H5,MATCH(C3,G2:G5,0)),"")
C5,C13,C9C5=IF(C4="",0,"")
C8C8=IFERROR(INDEX(K2:K6,MATCH(C7,J2:J6,0)),"")
C12C12=IFERROR(INDEX(A3_,MATCH(C11,Q3_,0)),"")
Named Ranges
NameRefers ToCells
A3_=Sheet1!$N$2:$N$5C12
Q3_=Sheet1!$M$2:$M$5C12
Cells with Data Validation
CellAllowCriteria
C3List=$G$2:$G$5
C7List=$J$2:$J$6
C11List=Q3_
 

Forum statistics

Threads
1,143,835
Messages
5,721,067
Members
422,339
Latest member
SHIVATVM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top