Controlled access to spreadsheet

[Jeff]

Good Morning (Afternoon or Night),

I have a Excel 2000 spreadsheet on a W/2000pro network. There are 20 users
that need to have read-only access and 5 that should have read/write access.

With the traditional read-only security the 20 users can open the document
in read-only mode but still end up saving back over the original document.
These 20 users do need to have full abilities once in the document (filter,
sort, subtotal, etc). In addition, the 5 users will be making updates on a
nearly continuous basis (30-60 entries a day).

Ultimately, it would be perfect to have a "Ghost" of the spreadsheet
somewhere else that is linked back to the original, but I am not sure how to
go about that since the original will be getting new rows added all the
time. I know one solution would be to lock down the network directory for
read/write and read-only access. The problem is that I will need to apply
this type of security to many spreadsheets in several directories with users
have read-only access in one directory but full access in another; this
would be a nightmare to maintain all of these relationships.

Your assistance is appreciated.

Jeff Meyers

 

[cerfingnow]

I don't have time to get into total details, but perhaps I can point yo
in a direction that you can investigate.

You can password protect the file for edit by going to Tools->Options
then select the Security tab...you will see the field for setting
password to allow modification to the file. It sounds like you ar
doing this already, so may not be news.

You can also allow the 5 users to open simultaneously by selectin
Tools->Share Workbook and checking the Allow more than one user..
check box. Furthermore you can protect parts of a worksheet with
password (check out the online help for more on this)

As for a "ghost" file. Try this, open your main workbook, then open
new spreadsheet. in A1 of the new sheet, type "=" then navigate to th
original workbook, point to cell A1 of that sheet. The formula ba
should show:

='[original workbook]sheet1'!$A$1

Where original workbook will be replaced with the name of your workboo
and sheet1 will be replaced by the actual name of your sheet. Befor
hitting enter, use the F4 key to change the absolute $A$1 to a relativ
A1 (or type over manually). After you hit enter, you will be in you
new work sheet and cell A1 should have the formula:

='[original workbook]sheet1'!A1

Now, edit this formula to read:

=If('[original workbook]sheet1'!A1="","",'[origina
workbook]sheet1'!A1)

This will keep blank cells from showing "0".

Almost done...now copy this cell to every cell for the entire range o
the original workbook. To get the formatting the same, go to you
original workbook, select the entire range and "copy". Go to cell A
in your new workbook and do a "paste special". Select paste colum
widths for the first time. Then do "paste special" a second time bu
paste formats.

This new file can be marked read only etc. to allow your 20 users t
read the file. Be sure to protect the sheet so no one can modify you
link formulas. Also, you may want to go into Edit->Links and disabl
the automatic prompt that will ask if the file should be update
everytime it opens. Play with it a while and you'll see what I mean.

There are some gotchas depending on other conditions I don't kno
about, but again, something new to investigate for you. Hope i
helps...

Good luck