Smart questions
Smart answers
Smart people
Join Tek-Tips Forums
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

EliseFreedman (Programmer) (OP)
18 Mar 10 12:18
Hi There

We are trying to design a questionnaire which will be sent out to members of staff. It is based on their carbon footprint.

I have been asked to look at different ways of doing this using IT.

I had a look at what was available on the internet but everything i found had a cost attached to it.

I then had a look at MS Access and whilst it does what we would like, it is not an option at this point as we do not have a server available on which we can put the database/data access page.

I think we can do most of what we want using Excel.  We can customise the questions based on the responses given and we can do calculations.  We can also issue the questionnaires via email and have them sent back to us by email.  My one problem that I need to resolve is how to log the responses that we recieve into either another excel worksheet or an access database.  I have found code which copies ranges from spreadsheets into a workbook but Im not sure how to deal with the fact that not every cell will contain the answer to a question. How do I only select specific cells?  Any help would be appreciated
SkipVought (Programmer)
18 Mar 10 12:27


Elsie,

Quote:

Im not sure how to deal with the fact that not every cell will contain the answer to a question. How do I only select specific cells?
What is the STRUCTURE of the data?

This will determine how you will approch the problem.

Please post an illustrative sample.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

EliseFreedman (Programmer) (OP)
18 Mar 10 12:51
skip, below is a rough structure of my data. The problem is that the data is not laid out in sequential rows.  e.g The name will be inserted in Cell B1. Row 2 will be completely blank then the clock number will be inserted in Cell B3 then row 4 will be blank etc.
 
Name    

Clock No    

Email Address    

Department    

Contact Number    



Where do you propose to make savings    At Home/At Work/Travel

How do You propose to save CO2    Drop Down List of Choices


options chosen    standard savings    your savings
 
SkipVought (Programmer)
18 Mar 10 13:22


You, or whoever designed the questionaire, may not have given much thought to how the results would be gathered. But it may have made your task somwhat difficult, or more difficut than it need to be.

I would have placed the answers in a Named Range.  You might still be able to do that.  I would also put the data containing WHO the form cam e from in a Named Range.

Then as you receive them, move the workbooks or eMails to a separate folder.  It will be possible to loop thru the folder and query or copy the Named Range areas to a consolidated table for analysis.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

Gavona (TechnicalUser)
18 Mar 10 17:25

Quote:

We are trying to design a questionnaire
So you have the opportunity to design the questionnaire.

Why have blank rows?  Change the height of the rows that do have data in them to achieve the spacing you want.

From what you have shared so far I see no reason why all the answers could not be in a single column.
Nothing else in the column.

The area containing the answers should be a named range.

The workbook should be protected to prevent changes to the structure.  Only the cells where you expect answers should be unlocked.

The essence of your routine could be (Skip may have better ideas!):
Open the return,turn off protection, select the named range, apply autofilter and filter for blanks in this column.
Delete these blank rows then remove the autofilter.
Copy the named range to your analysis workbook.  I would suggest using PasteSpecial, transpose.  In this way all the answers to a particular question would be in a column.  This would give you the best chance of summarising/analysing.  For questions with a limited number of answers a pivot table could tell you how many of each answer.....

Gavin

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close