MS Excel/Access Questionnaire application

[EliseFreedman]

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]

Elsie,

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[EliseFreedman]

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]

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,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

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