• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 717
  • Last Modified:

Excel Questionnaire

Excel Gurus... Need some help.

I have an excel based questionnaire dealing with security risk in an organization. All answers are either a "yes" or "no"

via a drop down choice list (data validation). The main idea is if any of the answers are "No" then I want to auto generate

"canned" responses in a dashboard (Worksheet3). I have built out the excel template and attached it here.

Worksheet1 (questions)

Physical
1. Do you have a security Camera?
2. Do you have locks on your doors?
3. Do you own a guard dog
4. Do you have a guard onsite?
5. Do you own a gun?

Technical
6. Do you lock your workstations at night?
7. Does your computer lockout after a predetermined time?
8. Do you change your passwords frequently?
9. Do you lock your workstation to the desk?
10.Do you utlize disk encryption?

(possible more groups in future)

Worksheet2 (Response Repository)

Physical
1. Security Cameras: The insurance company strongly recommends adding cameras in the workplace
2. Door Locks: Locks are really important, we suggest getting some.
3. Dogs: We suggest a dog or two
4. On Site Security: We suggest that you get a rent a cop
5. Weapons: We suggest buy lots of guns. What could go wrong?

Technical
6. Locking Workstations: Best practice dictates that you lock your workstation when you walk away from it.
7. Computer lockout: We recommend that the workstation auto locks after 7 minutes of idle use.
8. Frequent Password Changes: We recommend you change your password every 30 days.
9. Workstation locks: we suggested bolting down the computer so no one can steal it.
10. Disk Encryption: We suggest installing a disk encryption program.

Worksheet3 (The dashboard)

SO, for example, If the person chose

1.No
2.Yes
3.No
4.Yes
5.No
6.Yes
7.No
8.Yes
9.Yes
10.No

The report would auto update the following (just "no's) in Worksheet3

Example continued:

---------------

Phyiscal

2.Do you have locks on your doors?


4.Do you have a guard onsite?
On Site Security: We suggest that you get a rent a cop


Technical

6.Do you lock your workstations at night?
Locking Workstations: Best practice dictates that you lock your workstation when you walk away from it.

8.Do you change your passwords frequently?
8. Frequent Password Changes: We recommend you change your password every 30 days.

---------------


BONUS: I would like to give each question (worksheet1) a value rating in a hidden field. This hidden field assigns a "weight" to the question for each category.  In this case "1" is the most important question in that group. The idea here is if the user selects "no" on every question (I really don’t want to have 10 responses since that user probably wont read them all). I would like to make sure that the first 3 "no" answers, filtered by weight is listed.  Example, the user says "no" to numbers 1, 2,3, 5,.  Only Response 1-3 will be listed. If the user selects 3<=  questions, regardless of weight all questions 3< will be listed .
Questionnaire.xls
0
Tbone007
Asked:
Tbone007
  • 4
  • 3
1 Solution
 
Richard DanekeTrainerCommented:
I used a direct If statement to hide or to show the response.  Then, there is no need for Sheet2.

To emphasize the importance of the 1 responses, they are in BOLD
Questionnaire1.xls
0
 
regmigrantCommented:
This doesnt use the weighting in the selection but if you list in order of importance - which presumably you would do - you can use the hidden field as a priority instead

Reg

Questionnaire-1-.xls
0
 
Tbone007Author Commented:
Regmigrant - Nice. One additional question. Is there a way to have a little control panel (on another worksheet) that gives me the option (perhaps a drop down list) to only list the "top weighted" 1, 2, 3, 4, or "ALL" responses? Thanks in advance.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
regmigrantCommented:
control panel is a grand word for what I've added but I think it does the job - J4 lets you set how many results are shown but note that its  one number across all sections. Its easier enough to fix for separate sections if that would improve things


Questionnaire-1-.xls
0
 
Tbone007Author Commented:
Sweet. Thanks Regmigrant. You'll get the points (although you totally deserve $ !).
ONE last question (I promise). On Worksheet3 is where I want to layout the report. I am not sure if I am stating this correctly, but I want to list out all the no answers. If I do a bunch of IF statements then there will be blank lines between those responses, for example:

1. Response, blah, blah, blah

3. Response, blah, blah, blah

5. Response, blah, blah, blah

How can I dynamically list these so there are no blank lines between questions?

1. Response, blah, blah, blah
3. Response, blah, blah, blah
5. Response, blah, blah, blah
0
 
regmigrantCommented:
There isnt a simple formula for that which would work across all the categories and be consistent. You can use sort on each section at one time or you can use a filter criteria or build a macro.

Or you can use a pivot table as I have done here - which is a bit of a sledgehammer to crack a nut but can at least be easily formatted and updated.


Questionnaire-1--1-.xls
0
 
Tbone007Author Commented:
Thanks Regmigrant.
0
 
Tbone007Author Commented:
Awesome Job.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now