How do I link comments to a drop down list?

[MonieB]

I'm in the process of composing a questionnaire in Excel. I've got my questions all in order and I've created a drop down list in the cell adjacent to each question allowing a choice of answers. I have a list of comments each realting to every possible answer to all the questions and want to find a way of getting the corresponding comment to automatically show up in the cell next to the drop down list when an answer is selected from it. I've seen it done before but don't know the theory behind it and cannot find any advice online.

Thanks in advance!!

 

[AnotherHiggins]

Use several different lists - one for each question. I'm assuming you have the list on a hidden sheet, which means you're already using Named Ranges for the validation. So use meaningful names for each Named Range to make it easier to line them up to the corresponding questions.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[MonieB]

Thanks for your reply, however I'm not sure we're on the same page... I've already got the drop down lists with the answers in on the sheet. One list for each of the questison - and yes they're from data on another sheet with the ranges named Question1 etc. What I need to do now is to find a way for the relevant comment (relating to the answer selected) is displayed in the next cell when an answer is selected from one of the drop down lists.

Thanks

 

[AnotherHiggins]

Oh, I see.

There are multiple ways to proceed. But only one way comes to mind without using VBA (macros).

You obviously don't want test-takers to be able to click into the 3rd column and see the answers, so just using a formula won't work.... Unless you lock down the 3rd column so that no one can click into it.

What version of Excel do you have? I know the following is available in Excel 2003 forward - I don't remember about 2000, and I'm not at the office to check.

Let's say your questions are in A1:A10, your dropdowns with answers are in B1:B10 and you want to show whether the answer is correct in C1:C10.

First, you'd put formulas in C1:C10 - something like:
[tab]=If(B1="","",If(B1="a","Correct!","Try again"))

Now select A1:B10, go to Format > Cells and on the Protection tab, uncheck Locked.

Now go to Tools > Protection > Protect Sheet, enter the password of your choosing and uncheck Select Locked Cells.

Now the user can select the answers from the drop down. The next cell will tell them whether or not they got the right answer. But they will not be able to click into any cells in column C, so they won't be able to see the correct answer burried within the formula.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.