Editing Excel by multiple users simultaneously off on-premise File Server

Hi,
 
 When I set up my EXCEL file in MS OneDrive(Cloud), I can share it with other users (whether they are on the same network or not) and it allows multiple users to open and edit same EXCEL file simultaneously.
 How can I accomplish the same thing when the EXCEL file is located on the local file server?
 As you know, when I open the EXCEL file and when someone else tries to open it, they will see "read-only" warning.
 Is there a way to allow multiple users to open the same EXCEL file and keep it on the local file server instead of OneDrive/Cloud drive?

Thanks.
LVL 1
sgleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
There is an in-built feature of Microsoft Excel allowing workbooks to be "Shared" for multi-user collaboration:

[ https://support.office.com/en-GB/article/Use-a-shared-workbook-to-collaborate-b8207fe9-a613-483f-b804-0ca4658b0c61 ]

However, it is a widely-held premise, especially with "Experts" at this site, that there are two states of a shared workbook:

a) One that has caused data within it to be corrupted, or
b) One that has not corrupted data... yet.

That said, I have provided (read: had the misfortune to use against my better judgement) shared workbooks to my clients on a number of occasions &, although issues did occur from time-to-time, as long as regular backups of workbook files were taken (usually on an hourly basis), the amount of rework necessary in the event of corruption is kept to a minimum.

The practical usage of shared workbooks is debatable, as they can cause issues in a variety of ways, especially as the number of concurrent users increases.

However, these issues can be reduced or, at least, the frequency of their occurrence can be limited, by writing additional Visual Basic for Application code, or approaching the design of a workbook's implementation (&, hence, code) differently to single/exclusive-use workbooks.

Shared workbooks do have limitations; some of which can be overcome with alternate approaches, but without prior knowledge of exactly what your workbook is doing, it will be difficult to advise you.

Just for background reading or, perhaps for you to understand why you have the problem you described, here is a summary of what features are unavailable (in MS-Excel 2007 & MS-Excel 2010) when a workbook is shared:

[ https://support.office.com/en-ca/article/Use-a-shared-workbook-to-collaborate-49b833c0-873b-48d8-8bf2-c1c59a628534#bmshareworkbook ]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Michael FowlerSolutions ConsultantCommented:
Personally I think the best answer here is to store the data in an Access database and then link the excel spreadsheets to this, even Microsoft likes this solution
https://support.office.com/en-nz/article/Top-10-reasons-to-use-Access-with-Excel-2a454445-13cc-4b39-bc2f-d27fd12ca414

Here is an example of this can be done
http://www.howtogeek.com/howto/9567/share-access-data-with-excel-in-office-2010/
0
sgleeAuthor Commented:
@[ fanpages ]
Thanks for the information and I will read upon this.


@Michael74
I write programs in MS Access and VB. I agree that ACCESS is a better choice if it is going to be accessed/edited by multiple users simultaneously.  In this particular case, EXCEL is the choice that users made for simplistic standpoint and I will to find the solution that will allow users to access EXCEL file all at the same time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.