Advice on forms and database

Hello all. Let me start by saying that I am a complete novice in the world of excel.
I work in a small company who, for compliance need to complete a small set of yes/no type questions. We complete about a dozen of these each week on a paper form and then the answers are then input into excel to keep the results and create some colourful graphs.I have been charged with trying to improve this process!
What I would like some help on, is how I can replicate the form on an excel sheet and get the data that is input in the form, to somehow store itself in a seperste area so that we can keep a complete record.
I have tried numerous searches on the Microsoft site, but I'm afraid that I'm really not understanding and am hoping that someone may be able to give me a real idiot guide type explanation on how I can about this little project.
We use offIce 2010, which I think is important to let you know. If anyone can help I would be extremely grateful.
Thanks in advance.

When you say you want to save the data in a seperate area, do you mean you want to get the data into a database like Access or do you mean you want to get the form data onto a seperate worksheet?

I would like to keep it in excel in a kind of database, so that we can continue using the same sort or reporting process.
By different area, I just meant another spreadsheet or the like do that when someone completes a form online, they only deal with that form and not the collected data.
Thanks again.

Take a look at this and see if this may be close to what you need.

Note:
the number of questions can be adjusted
you answer by clicking in the cell and you just select yes or no.
i added a date and time for each question so you could sort or filter the data on sheet2

This contains Macro code so you will need to allow or enable macros for this to work

click the clear answers button to ready form to be filled out. when answers are complete click the save form button. it will copy the data over into
sheet 2 into the next available row.

Thanks very much for the attachment. I haven't actually got 2010 at home, so I will take a look at work today. I really appreciate your help and I'll update again later. Thanks again.

Ok, this is just the sort of thing, but now having opened it and played around a little I have a couple more questions if I may :
1.If I add a line it only takes the first ten lines into sheet 2
2.The date field automatically fills in the current date and time on all records in sheet 2, can it be held at the original date and time?
3.How can I access the macros to try and get a better understanding of what goes where and how they work?

Thanks agaIn for your help so far.

Sorry I jumped in without doing some proper research. Therefore please ignore questions 1 & 3, but I'd still appreciate some advice on Q2.

Thanks in advance.

OOOOPS .... I forgot about it taking the date formula on the copy/paste. We can get around that by just pasting the values only . you will lose the color format but that probably is no big issue, i am sure you are more concerned with getting the data in there . I also added some safety valves in the code. i know i like having these in place myself. It will pop a message box up asking if you want to continue with the job. sometimes you click without thinking and this just makes you stop and make sure you want to go ahead with the operation. If you got any other questions or format changes you need to make let me know i don't mind helping.

updated the code a litle more for you.... I thought in case you have to add questions in the future this file keeps you from manually having to change the code to copy the new rows you added, the code will pickup the new rows and copy/paste them automatically.

Once again thanks for getting back to me and for the time you have obviously invested in my little project. I'm at home again now, so unable to look at them, but I will do first thing in the morning
Thanks again, it's much appreciated.

I just wanted to say thank-you for all of your help with this. I have now got the spreadsheet ready to go and fully expect it to be approved today.
The only downside is that now I've delivered this, they will probably expect so much more
Your help is really appreciated, thanks again!

Hello again.
It's all working fine but I have one little cosmetic query. Ideally we would like to be able to protect the spreadsheet as a whole and the pre-defined cells. The trouble I'm having is that the clear function won't run when I have protection on. Is there any way around this?
Thanks in advance.

No problem . just add the ActiveSheet.Unprotect at the start of the clear sheet code ( this will allow the clear contents). add the Active Sheet.Protect line at the end of the clear sheet code ( this turns protection back on).


ActiveSheet.Unprotect

ActiveSheet.Protect

Thanks, as always for your help. I've put the commands in place but when I go through the process it asks for the password prior to clearing. Can this be avoided or have I done something wrong with the commands?

here you go, just replace the word password with your password. make sure to leave the quotation marks in place as well.



ActiveSheet.Unprotect "password"

ActiveSheet.Protect "password"

That's perfect.
Thanks again for all of your help, it really is appreciated.