Sharing a big spreadsheet

[Latexman]

I'm one of three people filling in data in a big (~17MB) spreadsheet on a timeline. Everyone works M-F 8-5. Frequently, someone cannot work because someone else has the spreadsheet open. It will also be unproductive to keep 3 copies and combine them in the end due to sequential reference numbers being assigned by us and the way the spreadsheet functions. We've heard that "sharing" is the answer, but we've also heard horror stories about sharing. What's your experience? Should we share, or not?

Good luck,
Latexman

 

[melone]

Can you convert your Excel database over to Access? Access seemlessly allows multiple users to manipulate the same file....

 

[Latexman]

No, none of the three people know Access.

Good luck,
Latexman

 

[IFRs]

In Excel go Tools / Share Workbook.
Check the advanced section also.

We have several spreadsheets shared in our office of 12. Everybody makes changes at the same time. When you press Save, it updates yours with their changes, etc.

The biggest one we share is about 5Mb, not sure about your 17Mb one...

 

[EGT01]

Latexman,

Having encountered similar problems accessing Excel files available over a network, I've often wondered about sharing but never really had that much problem to make it worth investigating the sharing option. I can't give any opinions regarding to share or not to share, but I'm curious, what kind of horror stories have you heard?

 

[Latexman]

What I heard is the spreadsheet gets corrupted and un-usable. You have to get the back-up from the previous day and start over. I've heard this now from two folks that have tried sharing. I'm leaning towards trying it, but we'll maintain a back-up at the end or beginning of each day so we won't have to get IT to get the previous days back-up for us. It'll be a hassle, but we have to do something to meet our timeline.


Good luck,
Latexman

 

[IFRs]

I attempt to backup our shared spreadsheets every hour during the day using SecondCopy. It will not copy a file in use but often enough it works. To date I have only needed the backup once ( in 3 years ).

 

[Latexman]

IFRs,

What is SecondCopy?

Good luck,
Latexman

 

[IFRs]

Latexman - It is an inexpensive backup program that I have been using for a few years to archive important files. See

http://www.centered.com/

 

[BillPSU]

All spreadsheets were originally designed to allow data entry and calculations to make life easy. Your application is that it is a data repository and being shared. Microsoft tried to address the problem with sharing and per the horror stories it is not very successful. A database with sharing capabilities is a better solution however no one wants to learn database programming. It is well worth the investment of time if this continues.
Several years ago I created several databases from downloaded mainframe data. An Item Master, a MRP data file, a BOM data file and a transaction log data file. The data was shared by many different people and then put into individual spreadsheets. The data was also to large for spreadsheets without reducing the size. The item master had over 100,000 records, the BOM 1,400,000 records, the MRP 45,000 records, and the transaction log 8,000,000 records.
If the application is that important the use of a database should justify the time and cost.

 

[Latexman]

BillPSU,

Yes, database is the best choice based on program functionality alone, but this application is going to be widespread in our business, i.e. every plant will need to do it (~ 30 plants). So, we have to cater to the skills of the masses, which means Excel and not Access.

Good luck,
Latexman

 

[johnwm]

For data security you need a proper database. Your guys can only handle an Excel frontend.

Use Excel for f/e with data stored/accessed using ADO from database backend. For this amount (and obvious importance) of data use MSDE for three users.

Good Luck
johnwm
________________________________________________________
To get the best from these forums read faq731-376 before posting

 

[BillPSU]

Latexman,
I assume you built this spreadsheet application and have a very good understanding on how it works. Well wait till someone in another plant overwrites a formula or deletes a row or column of the sheet, With thirty locations someone will screw up the sheet and you will be customer support from now on. You will be training people with varying levels of Excel expertise and rebuilding damage spread sheets. Been there, done that.
Good Luck!!

 

[Latexman]

Each plant will maintain their own copy with their own data. All cells that have a formula are password protected. Those cells with their data are not password protected. Sorting is done using VBA pushbutton. If they screw it up, they'll call IT for yesterday's back-up.

Good luck,
Latexman

 

[melone]

So if everybody keeps a local copy of the data, why would you want to share a workbook?

 

[Latexman]

In each plant, multiple people are working on it part-time each to complete the task.

Good luck,
Latexman