Creating a questionnaire in Excel 2010

[billythefather]

Hello everyone!

First time posting on this page after using it many times. I have a challenging form I was trying to create but I'm not to sure where to start. I know what i would like for it to do but just don't know how to get it there. I approached my university professor who insisted Excel was not capable of doing this form but i took that more like a challenge and decided to bring it here instead.

What I want to create is a questionnaire that will respond with new questions based on the answers given in the previous question.

FOR EXAMPLE:
Question 1
- Did you pick any apples today?

in a drop down, we would want two choices.
- Yes or No

Question 2
- If "Yes": How many pounds of apples did you pick today?
- Amount of pounds then inputted on the next cell by user. Lets use "5" for example.
- if "No": End questionnaire.

Result
- After asking these questions, then we will like to gather the information. in a new cell, in a paragraph form, we would like Excel to complete a message according to the user's answers.
- Example:
for the first scenario "5 pounds of apples were picked today" having "5" the volume that can change according to the answer on question 2.
For the second scenario "No apples were picked today" Based on question one answered as "no"

I am looking for ideas or just direction of how I can do this.

Thank you!

 

[djl304]

I created something similar for a department within my organization, but instead of using a form I just created the questions in individual cells and then used VBA to either lock or unlock additional questions based on the responses given previously. In this case, I would put your first question in A1 and then use data validation on B1 (set to Yes/No). If the user answered Yes to the first question, then the second question (in A2) and the response for the second question (B2) would become unlocked.

From there, I would create 2 answer pages on a different sheets. I would use VBA to show/hide the different worksheets based on the answer given to either the first or the second question. Here is a quick breakdown Assuming your response for picking apples is on Sheet2 and your response for not picking apples is on Sheet3.

On Sheet2 in A1, you will have this for a formula: =Sheet1!B2 & " pounds of apples were picked today"

On Sheet3 in A1, you will have: No apples were picked today

On Sheet1, you will have your first question in A1, and B1 will be a Yes/No dropdown with an unlocked cell. A2 will have your second question and B2 will have your second answer (if it is needed). Then you need to protect Sheet1 (so that only the user can select unlocked cells). At this point, the user should only be able to select B1. Then, use the following in Sheet1 (this will run when there are changes made on cells B1 and B2 on Sheet1, so it must be placed there instead of in a module).

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Target.Worksheet.Range("B1")) Is Nothing Then
    If Range("B1") = "Yes" Then
    Sheets("Sheet1").Unprotect
    Range("B2").Locked = False
    Sheets("Sheet1").Protect
    Else
    Sheets("Sheet1").Unprotect
    Range("B2").Locked = True
    Sheets("Sheet3").Visible = True
    Sheets("Sheet3").Select
    Sheets("Sheet3").Range("A1").Select
    Sheets("Sheet1").Visible = False
    Sheets("Sheet1").Protect
    End If
End If


If Not Intersect(Target, Target.Worksheet.Range("B2")) Is Nothing Then
    Sheets("Sheet2").Visible = True
    Sheets("Sheet2").Select
    Sheets("Sheet2").Range("A1").Select
    Sheets("Sheet1").Visible = False
    Sheets("Sheet1").Protect
End If
End Sub

When the user enters Yes in B1, the code will unlock cell B2 so that the user can input an answer. When they put in an answer in B2, it then closes Sheet1 and opens Sheet2 with the statement of how many pounds of apples were picked. If for B1 the user selected No, then it will close Sheet1 and open Sheet3, with the answer saying that no apples were picked.

 

[gvsoft]

Type your First Question in A1
Use Validation list in B1 as Yes/No

then

type following code in A2 for second question

=IF([B]B1[/B]="YES","How many pounds of apples did you pick today? ","")

user types answer for second question in B2

in the Cell where you want Summary in paragraph type

=IF([B]B1[/B]="YES",[B]B2 [/B]& " Apples Picked today.","No Apples picked today.")

hope this will help you, it's very basic.

i have prepared a sample workbook but unable to attach here.

 

[djl304]

Sorry for the double post, but I didn't want the first one getting out of hand. It sounds like your questionnaire won't be as simple as one or two questions. What I sent you should be enough code to get through the majority of a simple questionnaire. The methodology for expanding this is pretty simple. Here is a breakdown of what I was tasked with and how I thought it out.

Problem - Create a questionnaire with 7 sections of varying lengths (up to 20 questions in each section) with varying types of responses (some are text answers, some are checkboxes, some are yes/no, and so on). Users should only answer certain sections based on their answers to previous questions/sections. Create a summary section with scores based on answers given (This questionnaire was for a privacy and security department, so based on answers given a risk assessment score was given).

My approach - First map out how the responses would impact further questions or sections. If a Yes response required further explanation, then the explanation would only be unlocked if the answer to the previous question was locked. This resulted in a lot of If Then or Case statements. Once everything was mapped out, it was just a matter of creating the new statements to properly lock and unlock cells and ranges.

I also had to create a summary e-mail that would be filled out based on the responses given (similar to how you wanted a response of how many pounds of apples were picked). I created individual responses on different worksheets that would automatically populate based on previously entered information, and then the worksheets would open and close when necessary.

 

[billythefather]

When the user enters Yes in B1, the code will unlock cell B2 so that the user can input an answer. When they put in an answer in B2, it then closes Sheet1 and opens Sheet2 with the statement of how many pounds of apples were picked. If for B1 the user selected No, then it will close Sheet1 and open Sheet3, with the answer saying that no apples were picked.

This is the exactly what i needed to get started. I used the "apples" as an example. As you mentioned, I'm looking at adding about 20 to 30 different questions with different actions depending on the response but this gives me a great lead.

thank you!

 

[billythefather]

This is basic but definitely helpful. I think what I need the most is a result or grouping formula that would explain all previous selections or inputs.

 

[billythefather]

SI also had to create a summary e-mail that would be filled out based on the responses given (similar to how you wanted a response of how many pounds of apples were picked). I created individual responses on different worksheets that would automatically populate based on previously entered information, and then the worksheets would open and close when necessary.

Do you think instead of doing an email, I would be able to just summarize it in the form itself? Like a summary of everything the agent inputs using a pre-made template.

 

[djl304]

This is what I did. At the time, I didn't actually set up mine to send an e-mail. Instead, it generated the summary in a new worksheet that the user could copy and paste into an e-mail. You would just use the VBA that moves through the different worksheets to display the appropriate summaries.