Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel Questionnaire

Posted on 2011-10-26
8
Medium Priority
?
707 Views
Last Modified: 2012-05-12
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
Comment
Question by:Tbone007
  • 4
  • 3
8 Comments
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 37037124
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 37037153
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
 

Author Comment

by:Tbone007
ID: 37038512
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 quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 total points
ID: 37039111
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
 

Author Comment

by:Tbone007
ID: 37040809
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
 
LVL 19

Expert Comment

by:regmigrant
ID: 37045416
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
 

Author Comment

by:Tbone007
ID: 37046887
Thanks Regmigrant.
0
 

Author Closing Comment

by:Tbone007
ID: 37046892
Awesome Job.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an article on how to answer questions, earn points and become an expert.
MS Outlook undoubtedly is the most widely used email client.Its user-friendliness, cost effectiveness, and availability with Microsoft Office Suite make it the most popular email application.  Its compatibility with Microsoft applications like Exch…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question