How to handle multiple forms

[Bubuto]

Hi everybody, I have a little problem. I'm making an excel sheet, which includes questions and option buttons. I'm going to send it to a group of people, and their task is to fill in the sheet and answer the questions and then mail it back to me. The problem is that I don't know how the handle these answer easily. Of course I could check all the answers by myself and fill one data collection sheet manually, but is there anyway I could make excel do it for me? My idea was to merge the files into one file as different sheets, and then calculate all the data into one sheet. Is this possible or is there another way to achieve this?

 

[Colo]

Hi Bubuto,
I think it's possible via Macro.
Please let us have more details of your Worksheet.

 

[Bubuto]

Hi, the worksheets are pretty simple. They have a few questions, and all the questions have five possible answers, which are controlled by option buttons. There are also couple of textfields next to some of the questions. In the beginning of the form there are some basic questions, like from which country the person is from, and some other basic questions. First of all, I'd like to for example calculate the average answer for the first question in all the worksheets mailed to me. Then I'd also like to sort these by the country (and by some other options chosen in the first part of the sheet). The most important part for me is to calculate the averages in each of the questions. If there is a macro to do it for me, I'd be very grateful!

Thank you for helping!
Petteri Toukoniitty

 

[Colo]

Okay Bubuto, I understood what you would like to do.
First, save all xl files into an one folder.
Second, make each one of data into one worksheet.(Via macro)
I guess, It makes your calculation easier.

To write VBA code(Macro), we need to know the layout of your worksheet.
Eg.
Row 1 = Title
ColumnA = questions
ColumnB = answer1
ColumnC = answer2

Book2
	A	B	C	D
1	Question	Answer1	Answer2
2	whichcountrythepersonisfrom?	USA	Something
3
4
5	Whatisit?	Excel
6	Whoisthat?	Me?
7
Sheet1

 

[Bubuto]

Well, the questions are all in one column, and option buttons are below them. The buttons are linked to a cell in a hidden column "N", and I should access those cells in that column in all the different workbooks sent to me. I also have drawn some text boxes after some questions. It would be great if I were able to collect the texts typed in those by some macro. The country question has it's answer in cell "G16". Is this enough information, or do you need more specific details?