Her's an approach that I haven't thoroughly tested, but may be a solution.

1) Store all student info in the file we'll call Master and set up one
sheet/range for each student plus a Totals sheet,

2) Provide each student with a file that links to the appropriate areas in
the Master,

3) Hide all student sheets in the Master and use ToolsProtect Workbook with
a check for Structure and apply a password

Each student file can be password protected which will not interfer with
updates from Master. Even though anyone can open Master they will not be able
to view anything but the Totals sheet (at least 1 sheet must remain unhidden)
unless they unhide the other sheets which they can't do without the password.

Hope this is useful |:)

"Stephen Larivee" wrote:

I am trying to help a friend with an Excel project. She is a teacher. We
are using Excel 2002.



She would like to have 30 files for 30 people on a network drive everyone
can access Each of the 30 files would be password protected. (Save As,
Tools, General Options). Each person could access her own file but no one
else's.



She would like me to create a Grand Total sheet that would take data from
each of the 30 files and link the data to show the overall totals. Everyone
could see the totals, but they could not see the individual scores each
teacher had.



I can do all of the above. The problem is that when you open the Grand
Total file which is not password protected, you are told that the links must
be updated. If you click to update, you are then prompted for all the
passwords. This is too much work for each person to do, plus it gives them
passwords to get into the 30 files so there goes your security.



I guess I am asking two contradictory things from Excel. First, I am saying
I want to password protect some data in the 30 files. But then I want Excel
to make the data available in the Grand Total sheet for anyone to see.



Does anyone have any suggestions on how I could set up Excel to achieve my
goals? Or am I asking the impossible?



TIA