I am trying to compile information from a group of users. How is the best way to do this?

Question

I am trying to compile information from a group of users. How is the best way to do this?

Anonymous

We are keeping track of education performance. I wanted to try some automation. We are currently using spreadsheets that are sent to 40 to 50 people, 1 to 4 each. The spreadsheets are all the same except for the data loaded into them, but compliling them is a manual job. I am tryhing to have them all put in the same directory with a standard name format and then pulling them all into a single spreadsheet or database. Is this possible and how?

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

0 additional answers

Answer 1

Anonymous

Generally speaking you are best off with a database. If you intention is to compile it into a database anyway why not just start there... It will handle all of the concurency and consolidation for you.

0 comments

Answer 2

Here is one possible way:-

Have one Workbook only with 50 identical Worksheets (representing the 50 people).

Each person to complete their own Worksheet within the single Workbook.

51st Worksheet should total the others (automatically).

This entire job might be better handled in Access.

If my comments have helped please vote as helpful.

Thanks.

Answer 3

I am going to assume that the 40 or 50 people are separated by lots of miles and may not have a common network to provide them access to a database; that it may be impracticle from several standpoints to develop/install a database for the purpose; and/or the 40 or 50 people, or a good number of them, may be terribly afraid of databases but get along comfortably with Excel.

To answer your basic question: "Is this possible and how?" ... Short answer is YES, and you are well on the way to getting there by deciding to put them all into a single folder/directory with a standard naming format. The naming format isn't really critical, as long as you don't "step on any toes" and overwrite one person's file with another's.

But we will presume that internally, the workbooks are all the same: same worksheet names used for the same purpose(s), and data entry layout the same on each page.

At this point, it becomes easy to import the data in one of several ways, such as exporting to .txt or .csv files for import into a central workbook or database. But doing so by hand could be a bit tedious and it would be fairly easy to use VBA code (a macro) to deal with the tedium.

If you want to get with me to discuss this possibility, click my name to view my profile and my email address and instructions for contacting me are there.

Share via

I am trying to compile information from a group of users. How is the best way to do this?

0 additional answers