Excel write access by 2 users

[JedClampett]

There is a situation at work where 2 users need simultaneous write access to the same excel spreadsheet on the LAN. At the moment the first user to connect has both read/write access. If another user accesses the spreadsheet they get read-only access.

Is there some way to fix this so both users have get read/write access at the same time please? They are both taking telephone calls, and want to update a booking calendar spreadsheet in real time.

TIA

[submix8c]

NOT a good idea AT ALL! Period!

Scenario -

PersonA and PersonB both open FileCopyA for Update.

PersonA Changes and Updates which creates FileCopyB.

PersonB Changes and Updates which creates FileCopyC.

Changes made by PersonA to create FilecopyB are lost due to PersonB Changes.

This type of scenario is STOPPED in a Mainframe environment in the same way by using Enqueue/Dequeue (Lock/Unlock) techniques and a Comparison Routine to ensure overwrite/dataloss does not occur AT THE LINE ITEM (RECORD) LEVEL and NOT at the Whole File Level.

Your only option is to have BOTH Persons with their OWN copy and Compare/Merge Changes later.

Please understand that each person is working at the File Level and NOT on the Line Item Level. That's why they invented DataBases. Maybe you should change to using an SQL Database?

edit - Also note that MS INTENTIONALLY did that for the very reason I gave above.

Edited May 4, 2013 by submix8c

[jaclaz]

There is a situation at work where 2 users need simultaneous write access to the same excel spreadsheet on the LAN.

NO.

There is a situation at your workplace where the only solution devised for a problem has been that of having two users access the same spreadsheet simultaneously.

You are slipping on a chocolate covered banana :

http://homepage.ntlworld.com./jonathan.deboynepollard/FGA/put-down-the-chocolate-covered-banana.html

And now, for some recreational Zen, let me cite Master Foo (actually Sean Mc Grath):

... databases are Rocks spreadsheets are Water ...

http://www.propylon.com/news/ctoarticles/051115_master_foo.html

jaclaz

Edited May 4, 2013 by jaclaz

[JedClampett]

I agree totally with both replies. I'm not happy with using Excel to perform things that a spreadsheet program was not initially designed to do. Regarding the two user write issue I agree with this as well. What they really need is - as has been mentioned, some sort of database calendar/booking system that will scale well. So this was just a quick fix to keep them happy until a more suitable solution has been implemented. I'll tell them it's not designed to have two user's with write privileges active at the same time.

Update: I have found this here:

http://www.atoolspro.com/

http://www.softpedia.com/get/Office-tools/Other-Office-Tools/A-Tools-Free-Edition.shtml

but it looks quite complicated to set up to me. This may be useful to somebody else.

Edited May 5, 2013 by JedClampett

[jaclaz]

The tool you found is one that makes the spreadsheet "behave" as a database, it doesn't seem too difficult to install/configure but most probably it is not the "ideal" solution, it would probably be easier to see if something more "vertical/dedicated" *like*:

http://sourceforge.net/projects/bookingcalendar/

http://www.php.brickhost.com/

(just examples), more here:

http://sourceforge.net/directory/business-enterprise/scheduling/resource-booking/freshness:recently-updated/

would do for your problem.

jaclaz

[JedClampett]

Thanks for those links jaclaz. I'll take a look at them soon. I don't like the idea of using an excel spreadsheet like a database either. As a temporary measure, what if the current spreadsheet was split into two seperate sheets? That way one of the operators can have half of the contractors in their own spreadsheet, and the other call operator can have the other half of the contractors in their spreadsheet. They can both have read/write access then without getting locked out from making updates to their calendars.

[jaclaz]

Thanks for those links jaclaz. I'll take a look at them soon. I don't like the idea of using an excel spreadsheet like a database either. As a temporary measure, what if the current spreadsheet was split into two seperate sheets? That way one of the operators can have half of the contractors in their own spreadsheet, and the other call operator can have the other half of the contractors in their spreadsheet. They can both have read/write access then without getting locked out from making updates to their calendars.

Most probably you can also "cross-link" the two files.

I have NO idea how currently your spreadsheet is made and how the data is input in that, but check the attached Calendar1.xls and Calendar2.xls.

(of course just an example) when some data is entered in calendar1 the corresponding cell in calendar2 becomes red....

Just a basic idea, then you might need a third spreadsheet to "merge" the calendar of the first two (at the end of the day).

jaclaz

calendars.zip

[JedClampett]

Thanks for those replies. I've decided to build a dedicated Linux server, and use one of the PHP calendar booking systems. This needs to scale so we can have ten or more people using the booking calendar. So the excel thing was just a short-term fix that I don't really want to get involved with. Thanks again for all the input on this.

Jed