0

I need to make an ongoing database for a questionnaire with 12 questions. The tick box options are Certainly True, Somewhat True, Not True and Dont Know.Each of these has a value. So if you select certainly true its equal to 3 points. How can I display this in excel so that you can select the option and it assigns the value so that at the end of the questions it sums the total of each options value?

I hope that makes sense. Its not something I'm even vaguely familiar with and I have not found anything helpful by googling. I keep seeing to use v lookup but I don't what to find things on the spreadsheet. It needs to be drop-down selected of the option which automatically picks up how many points the option is worth and can total it at the end of the row.

Many thanks.

Improve this question
1
  • 1
    Excel is not a database, it's a spreadsheet. Don't use a spreadsheet to do tasks more suited to a database.
    – Tetsujin
    Commented Feb 8, 2021 at 18:09

1 Answer 1

Reset to default
0

Pretty simply in excel, but maybe you don't want to show people how each answer scores. You can always hide columns if you want.

In sample below, cells A2:A5 are the list reference. In column B, I just placed an arbitrary score for each answer. Normally this would be in another sheet, but keeping it here for simplicity.

Column D is your question, and Column E is the answer which the user selects from the dropdown list.

Formula for cell F2:

=IFERROR(VLOOKUP(E2,A:B,2,FALSE),"")

Drag it down, and you're done.

enter image description here

**** UPDATING THE ANSWER PER YOUR COMMENTS ****

Still use a vlookup if you want your questions in columns rather than rows.

Cell E3 formula:

=IFERROR(VLOOKUP(E2,$A:$B,2,FALSE),"")

Drag it across. That's it.

enter image description here

Improve this answer
2
  • Thanks this has been really helpful. Would it be possible to do this if the questions were in cells across row 1 and the answers underneath? Eg column D2 being I like cold weather and then the answer not true in column D3? Would H lookup be best for this format? I basically need to put the persons name in column A and then have their answers on the same row. I am making each row the heading with the rows underneath being a person and their answers/scores.
    – Nicole
    Commented Feb 8, 2021 at 19:51
  • @Nicole updated the answer. You can still use the vlookup formula.
    – Isolated
    Commented Feb 8, 2021 at 21:40

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .