Questionnaire - How can I automate the selected answers?

[Tayo]

Dear All,

I am trying to set up a questionnaire with the following conditions:

There are 3 set of rows with different questions, each question can only
have one chosen answer out of 3 possible answers.


Possible Answers
Agree Indifferent
Disagree

Question 1:
Question 2:
Question 3:

Now the issue is that for the 3 questions, you can only select 1 agree, 1
disagree and 1 indifferent as answer. In other words, you can't have more
than 1 agree, 1 disagree and 1 indifferent in each of the 3 set of questions.
Please, what is the best way to go about this?

Here is a sample for further clarification:

Possible Answers
Agree Indifferent
Disagree

Question 1: Yes
Question 2:
Yes
Question 3: Yes

 

[Alojz]

Here's my suggestion:
Do not have english version so do not know exactly the english names of menu
items.
Think it´s called data auditing, u can find it in data menu. Go to this
menu, set criteria to: allow list, to the source box type: agree, disagree,
indifferent.
U should be done. Or, create list somewhere vertically. Then assign ur
source list to source box. U need actually one column for answering the
questions.

 

[Tayo]

Hi Alojz,
Thanks for your response. Yes I know about what you are talking about (Data
Validation) but it won't solve the issue of entering same answer twice within
the 3 questions. What I mean is that somebody can now answer Agree in
qusetion 1 and answer Agree again in question 2. I don't want this. I want
to make sure that you can only select one type of answer once within the set
of 3 questions. I hope you understand my explanation.

 

[Gord Dibben]

Assuming you have Data Validation dropdowns with the three possible answers
to pick from

Debra Dalgleish shows how to hide an item in the dropdown once it has been
entered.

http://www.contextures.on.ca/xlDataVal03.html


Gord Dibben MS Excel MVP

 

[Alojz]

Sorry I misunderstood ur question.

I have a solution now, but little bit time consuming.
Create a 3sets of radio buttons (but radio button for forms, not active X)-
each set for one question. Put each set to the frame.
Each set (buttons in each set) assign to particular cell (right click on
button and choose format, last tab in format dialogue). Set 1 to one cell,
set 2 to second cell and set 3 to third cell.
Sum of this three auxiliary cells, if the form is filled correctly must be 6.
Through conditional formatting (for instance) give the respondent the
warning that questionnaire is not filled correctly (when the sum of three
aux. cells does not equal 6). It does not prevent from incorrectly filling
the form, but respndent at least would know that the form is filled
incorrectly.

 

[Alojz]

Hey, Gord!

this one was pretty cool, thx!

Assuming you have Data Validation dropdowns with the three possible answers
to pick from

Debra Dalgleish shows how to hide an item in the dropdown once it has been
entered.

http://www.contextures.on.ca/xlDataVal03.html


Gord Dibben MS Excel MVP

 

[Tayo]

Hi Gord,

Thanks for that link. I am very close to achieving my goal now but I need
further clearification on the following:

1. After choosing my answers for a set of 3 questions, the dropdown list
stop working and this will prevent me from changing my choice of answers if I
want to.

2. I have 10 main questions each with 3 sub-questions in the questionnaire.
Each of the 10 main questions will have 3 answers (Agree, Disagree and
Indifferent). Is there a way to automate this process so thatI won't need to
manually edit the code for each of the qustions?

For Alojz,
I would like to know more about the use of radio button on forms. I have
not used it before. Can you please give me a link of where to read more on it.
Thank you.

Please help me out.

 

[Gord Dibben]

That does pose a problem.

1. To change your input, you would have to clear contents on the entered
items to get the items back for selection.

2. I guess you could set up 10 independent tables of agree, disagree and
indifferent which is a lot of work but...............?

Maybe re-think and just tell users they are not allowed to have two answers
the same in any group of three.


Gord

 

[Alojz]

Tayo,

I cannot recommend anything particular for radio buttons usage. Once I just
saw my colleague's spreadsheet consisting buttons and made my own reserach
. Just try to create what I was suggesting - put 3 buttons in one frame
and assign it to one cell, you will see what they are doing - changing the
value of this cell depending on which button you press. To avoid another 3
buttons to change the same cell u have to make different frame for this new
group and redirect buttons from this group to another cell.

If it helps click on yes.

 

[Alojz]

Actually, one more, even simpe way. U just need to group 3 radio buttons in
one frame for each question, no need to assign to any cell, if u do not need
to make any calculations....

 

[Alojz]

Ooops, sorry, I forgot u need 3 different answers for 3 different questions.
So, u need auxiliary cells, as I wrote before, or, u can group buttons
wertically, what is weird. When u group it vertically, will avoid to give
same answer to each question but does not prevent to give to one guestion
more than one answer