Shared Workbooks

    Is there a limit on the number of users who can access and work within an Excel shared workbook at a given time? I've created a spreadsheet with some simple formulas applied to five of the columns (ie: If one field = X, display XX in another Field). There are 25 columns used with approximately 1000 rows. I first unprotected the spreadsheet, left five columns open so users could enter data, then protected the spreadsheet and the workbook.


    The problem is that we have approximately 50-70 users who could possibly hit this spreadsheet during the day. We're finding a lot of locking errors occurring and data is being lost or users are not able to save. Many times it will advise them to save under another name.


    Is the problem too many users, protecting the workbook or trying to save formulas inside the spreadsheet on this shared file?


    I'd appreicate any help that can be given quickly. Thank you.
    Terry

    Re: Shared Workbooks


    Terry,


    Sorry to say that Excel is not really a multi user environment application. I've personally believed that MSFT should never allowed that type of use.



    Your best alternative is ( yep I'm gonna say it ) use a database for data storage. Access is a better platform for storing the data when multiple users are involved. Though it is hamstrung ( because its an entry level database ) to not being able to handle alot of concurrent users and size of data being stored.



    The next alternative ( this will keep you in excel ) is that the data needs to be ported to an external workbook.


    If a user is just reading data open the workbook "ReadOnly".
    If a usre needs to add data then close and attempt to open "ReadWrite" if the workbook doesn't open "ReadWrite" then notify user updates can not be made at this time.


    Now the real question is this. Will 2 users be trying to make changes to the exact same record at the same time. This will pose problems and a database is designed to adress this issue much better than excel.

    Re: Shared Workbooks


    Two users would never be updating the same line at any time. Each user will have the lines they need to address identified within the spreadsheet. It's just that the information is received all in one listing and we're trying to have all of them access it at once. Agreed, it has been a receipe for disaster.


    Someone else suggested having users download the sheet, make their additions and save it as a separate file (with unique names) and then using macro's to pull that information back onto the original sheet. I've done that and it will serve the purpose. It's just a bit slower than expected, but it may be the best we can get.


    Thank you for your help and quick response.
    Terry

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login