I have to set up a data collection system. There are about 50 agents who will
enter information on 40 services in their area (but not everyone would need
to enter information on all of the services. Each agent would have a
collection of about 5-35 services that they deliver). I need to use Excel for
data collection. However, access could be used for collation of data and
further analysis. I am not very experienced in using VB but could probably
manage a bit of code.

The two options that I have thought so far a
1) to set up one spreadsheet with one tab for each 40 service and have a
system in place to collate all information on one sheet. Hence, I would have
only 50 spreadsheets, one from each agent. I could have a summary sheet that
I would collate later.
2) To set up one spreadsheet for each 40 service and each agent would fill
in the ones that they needed to. I am not sure how I would be able to collate
and manage such large number of spreadsheets. If all agents decided to
deliver all 40 services one year, I would have 2,000 spreadsheet to deal with!

Does anyone with more experience have good ideas how to design this to be as
effective as possible? Any comments or ideas welcomed!

Thanks in advance.