×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sharing a big spreadsheet

Sharing a big spreadsheet

Sharing a big spreadsheet

(OP)
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

RE: Sharing a big spreadsheet

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

RE: Sharing a big spreadsheet

(OP)
No, none of the three people know Access.

Good luck,
Latexman

RE: Sharing a big spreadsheet

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...

RE: Sharing a big spreadsheet

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?

RE: Sharing a big spreadsheet

(OP)
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

RE: Sharing a big spreadsheet

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 ).

RE: Sharing a big spreadsheet

(OP)
IFRs,

What is SecondCopy?

Good luck,
Latexman

RE: Sharing a big spreadsheet

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/

RE: Sharing a big spreadsheet

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.

RE: Sharing a big spreadsheet

(OP)
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

RE: Sharing a big spreadsheet

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

RE: Sharing a big spreadsheet

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!!

RE: Sharing a big spreadsheet

(OP)
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

RE: Sharing a big spreadsheet

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

RE: Sharing a big spreadsheet

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

Good luck,
Latexman

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close