Merging files, layout for data input

OK

If the ultimate requirement is a statistical analysis then I think you'll be better using Excel as the analysis tool rather than Access. From a purely professional advice point of view it would seem the best bet would be an Access database which would then be imported into Excel for subsequent analysis.

However from a pragmatic point of view and unless you really want to learn Access, a database in Excel is perfectly OK.

I see three initial key steps.

1 Decide exactly what data you want to collect and on a dedicated sheet create labels for each field of data on row 1. Don't forget to include fields like the record date, and the user name of the person who entered the data, and there may be other 'admin' type fields which you need. The important thing is to try and think of every conceivable bit of information you may want. Far better to get a complete list now than to have to worry about adding fields later on. Some fields may be calculated fields based on information in other fields. Include these now.

2. On another sheet design a record input sheet which should include a cell for every field of information, apart from calculated fields which can be worked out in the background.

3. Decide which of the input cells are simple free format fields and which should be restricted to permitted values only. You'll then need to create validation tables for all these allowable choices and create Data Validation options for these cells.

Once you've got all the basics designed then you can move on to the next stage, and I suggest you revisit the forum when you've got the basics set up.

The next stages will be to create macros which will respond to a data entry and add new records to the database, and the corollary - deleting records from the database.

At that stage the basic template should be available for releasing to the users.

the final stage will be to create a master statistical analysis workbook which will contain macros for loading all the data sheets from the individual data entry workbooks. You'll need to give consideration as to whether once a data set has been uploaded to the master workbook, it can be deleted from the originating workbook so that this starts out with no records ready for the next updating process, otherwise the system will need to test for duplicates before uploading to the master. However first things first.

HTH