Create a Survey in Excel

[ryankeast]

Hi There,

I have a satisfaction survey which I fill in manually for all my customers.

At the moment I am manually filling it in for 500+ people saving them individally then manually entering the results on a seperate spreadsheet.

I was wondering is there an easier way to do this in Excel.

So In theory I have my Survey on one page and every time I fill in the survey and save it I get the results on another worksheet.

Then with all the results in this second shhet be able to write a pivot table to get some statistical infomation?

Many Thanks.

Ryan

 

[ryankeast]

Sorry - so what I want to try and achieve is -

I have my Survey on one page and every time I fill in the survey and save it I get the results on another worksheet.

Then with all the results in this second sheet be able to write a pivot table to get some statistical infomation?

 

[SkipVought]

Hi,

ALL your results ought to be in ONE TABLE.

Check out Data/Form. It's not the most elegant, but it works.

Skip,

_[red][/red]

 

[DirkStruan]

Although Skip is correct, Data/Form I've found it is not always the best answer. Another option is to set up an input sheet. Then write or record a macro to tranfer the inputs to a second sheet. You can then use the tabulated data how you like.

A quick and easy approach is to have all questions in column A on sheet1 with all answers in column B. Also include a cell with the function "=counta(Sheet2!A:A)+1" in cell C1 (on sheet1), say. I would rename it "freecell".

Copy sheet 1 column A questions to row 1 of sheet2. (Copy the range, select A1 on sheet2, then edit menu, pastespecial, values & transpose).

In Visual Basic add the following macro

Sub Macro1()
Range("B1:B100").Select ' or whatever range of answers, assumed to be in a column format
Selection.Copy
Sheets("Sheet2").Select
Range("A" & Range("freecell")).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub


Next I would add a button to sheet 1 for ease (view menu, toolbars, control toolbox) and format as required. Assign the above macro to the button. Now fill in the answers in column B on sheet1 and click the button to add to the next sheet.

You can then add additional bits of code to clear the answers in column B on Sheet1, return to Sheet1 after the click etc.

Hope this helps

 

[fneily]

Don't know if you know how to do this, so I'll post it anyway. If you want a quick way to get percentage of satisfaction per question, try this:
Setup a list in columns A and B: Question# and Rating.
So use the above post to place your data in column A and B.
Sort list on Question#.
Setup, on the side, a table of questions and ratings such as
Question# Rating
1 As Advertised 1 Poor
2 Directions 2 Fair
3 Ease of Setup 3 Adequate
4 Waterproofing 4 Good
5 Excellent

Click in any cell in your data listing.
Choose Data, PivotTable. Click Next. Click Layout.

Drag Question field to Row. Rating To Column. Drag Rating again to Data.
Double click on the Sum Of Rating field. In the Name Box, type in Percentage of Rating.
In the Summarize By list, select Count.
Click Options. Open the Show Data As and select % Of Row.
Click Number. In the Category list, select Percentage and set Decimal places to 0.
Click Ok.
In the PivotTable Field box, click OK.
In the PivotTable Wizard box, click OK.
Select Exiting Worksheet and type $D$9.
Click Options. DeSelect Grand Totals For Columns and click OK.
Click Finish.

You'll now have a nice chart showing the percentage of each rating per question.