Shared workbook - losing data

[MO22]

I've set up a shared workbook for about 10 staff to enter into. The routine is that all staff updates have to be entered by end-Wednesday, so they work on it on Mon, Tues and Wed. There are constant complaints that users are "losing" their changes - ie. they were "there" on Wed afternoon, but don't appear in the spreadsheet when they open it on Thurs morning.

While I am sure someone is doing something wrong in saving, etc, has anyone had this problem with new entries disappearing? FYI, the users are not adding any new rows or columns, merely editing the existing cell data.

 

[xlhelp]

With a shared workbook you have who is first who is second kind of situation. If you and I both have it open and you save your work first and then if I save mine, your work is lost.

The better solution is not to share it. Then when you have it open, I would get a prompt saying it is already in use and do I want to open it as read only?

HTH

Indu

 

[MO22]

We tried not sharing it, but with 10 users on a tight deadline, there were too many fights over who was locking it up for too long!

As for the saving, I'm assuming you mean that if two users edit the same cell, only one will win out.

In our case, each row applies to a specific user - it is unlikely that any two users would be editing the same row/cell. They are instructed to only update their own contracts, and they usually filter on their own contracts to isolate their own data.

Also, when the spreadsheet is set up for editing on Monday mornings, the administrator sets Row1 and ColumnA as headings with Freeze Panes. At some point over the next few days, we will open the file to see Freeze Panes set at, say, Row50, even though all users have been warned not to change the freeze panes setting, and all users deny doing it, but I KNOW that it's not happening by itself, which leads me to believe that users may also be making mistakes in their save process. Cynical, aren't I?

 

[xlhelp]

Even if users have their own area to work in, you are still going to have the problem.

You may be better off using Access. That's the only app that will let you update info dynamically.

Indu

 

[mahasamatman]

MO22,

what xlhelp is talking about works like this ... if Al and Bob both have the spreadsheet open and make their changes, one of them will save the file first. If Al finishes and saves his info, and /then/ Bob saves his, the version of the file Bob is saving doesn't have the changes Al made, and is saved in place of the previous version of the file. So, Bob's changes are in the file now, but Al's are gone.

How about giving each of the ten users their own file to modify, and then pulling all of those into a master workbook?

 

[MO22]

I would LOVE to move this over to Access, but unfortunately, it arrives from overseas in Excel, we edit it, then mail it back overseas where they edit it themselves, mail it back and the cycle continues week to week. Ugh.

I understand now about the sharing overwriting itself, so we have discontinued that. I was under the impression that we could update at the same time, as Excel Help states "users work in the same workbook simultaneously".

So I will now try merging instead - any warnings about that process? Thanks

 

[AngO]

MO22,
How many copies will you be merging?
If only a couple - you shouldn't have much trouble.

If a lot of copies - make sure everyone has saved and closed before merging. Also, do all of the copies at the same time.

If you have 5 copies a,b,c,d,e and you merge only a,b,c the first time. Then add d and e later, you run the same risk as before. Excel doesn't really know who made the changes first, so any cell that was altered and accepted in the first merge will "win". Thus Excel will not look at those cells when you add the remaining two files later.

Hope this helps.
AngO