I wouldn't do what you describe with Excel. Although Excel is a splendid
application for analyzing and synthesizing information, it is as yet a poor
substitute for a true database, which can work very well when it comes to
serving multiple users at the same time and enabling them to supply new data
on the same fields.
Eventually, when your users have finished supplying their grades, you'll
prefer to be working with one file only, not a different file for each user.
In the latter case, you'll have to open the file, get that user's grades,
and move them to another file where you can aggregate and average all the
replies. With hundreds of users, that'll take a while.
Excel offers a way to do that -- that is, allowing users to edit a single
file -- but it's clumsy.
Using Excel only, Microsoft would expect that you would distribute to your
users shortcuts to a shared workbook -- that is, one that can be opened and
edited by multiple simultaneous users. You would make that workbook a shared
one by using Tools | Share Workbook, setting the options you want, and then
re-saving the workbook. In theory, it would now be possible for Tom, ****
and Harry to open the workbook, whether or not simultaneously, provide the
grades that you're after, and re-save it with the same name and path..
If the workbook is not shared, then the first user to open it can provide
the grades and save it to its original path and filename. But if Tom opens
it first, and either **** or Harry or both subsequently open it, **** and
Harry will be told that Tom's using the workbook and do they want to open it
as read-only or be notified when Tom's released it (presumably by saving his
changes and closing it). This message differs according to the version of
Excel that each user has. Unshared, as long as Tom has it open, **** and
Harry will not be able to make and save their changes to the same path and
filename.
Theory and reality differ. There are various problems with shared Excel
workbooks. If you are familiar with true database management systems (and
Excel is not a true database manager), you're better off sending your users
shortcuts to an Access (or some other) database, where they can open a table
and provide the grades you want. A true database manager is designed to
handle matters when Tom, **** and Harry want to edit or add records at the
same time.
After everyone's finished, you can copy-and-paste the data into Excel for
analysis -- or in a very complicated situation, you can pull the data
automatically into Excel by means of an external data range or a pivot
table.
--
C^2
Conrad Carlberg
Excel Sales Forecasting for Dummies, Wiley, 2005
"Ulf" <[email protected]> wrote in message
news:[email protected]...
> This might belong in Exchange rather than here but anyways:
> I need to send out a sheet with a number of things in need of being graded
> (1 to 5).
> When I get them back (several hundred) I need to get the average value
> (grade) of the returned answers. Some answers will not be entered and
hence
> shouldn't be used to calculate the average. Another one of the problems
here
> is that all the files returned might /might not have the same name.
Bookmarks