Data messed up / jumbled up in shared worksheet

[Fibon]

We are using Excel small business 2007. We shared a workbook with 12 users. Frequently the data will get jumbled up.

The workbook is used to keep records and update of jobs done. All information regarding a job will be in a row with different cells for Customer ID, PO No, PO Date, Item Code, Item Type, Sizes, Quantity, Job Progress etc etc. All these fields are in different column.

The Job Progress actually has many column of different processes to complete the job. Different users will update different components of a particular job as the job progresses.

In one of the columns, there are these Job Number in running sequence. The Job Numbers are alpha-numerical numbers and unique to each job. Any new jobs will be appended to the bottom of the spreadsheet with a unique new Job Number. Frequently, the jobs and the job numbers will get jumbled up. For example, a job with Job Number "A1234" will jump to a different row with say a different Job Number "A1100" etc.

We use filter in the table as well. Frequently different users will sort the table according to their preference. Some users may sort by Customer ID, Others may sort by Item Code etc etc. We ruled out the possibility that the messed up of data was due to sorting on part of the table and the other part didn't get sorted.

We do suspect it may have something to do with filter or sorting. Different users may sort or filter the spreadsheet differently at the same time. Could it be that when a user sorted the data say according to <Customer ID>, then appended a new record / job to the sheet and save it, the record may gone to a wrong row as other users may have sorted it differently?

Any suggestions what has gone wrong?

Many thanks
Fibon

[shekhar1660]

Hi,

Entire workbook is messed up with the different updations by the users. You all can follow the same approach to update the workbook.

[martindwilson]

its got to be someone sorting incorrectly.
shared workbooks can be a nightmare, ban them from sorting,changing fonts,colors,sizes anything at all if they want to do that make them take a copy do what they need there then update the master.
check the workbook daily and make sure every one has saved and exited.
make sure the same user is not listed multiple times as having the workbook open.

[Fibon]

Hi,

Entire workbook is messed up with the different updations by the users. You all can follow the same approach to update the workbook.

Hi Shekhar1600, actually every user update different sections of the spreadsheet. The first entry will be those job information. As the job progress, different user will update different stages of the progress under different column.

[vlady]

hello

can they edit other "column" not supposed to be their column for data entry? As martin pointing to.
if that's the case..
you can control which user can edit certain ranges using Tools > Protection > Allow users to edit ranges

or drastic move
"just a suggestion..."

You could use a different sheet (chop off the original every column) for each user and have different passwords on each sheet.
Then consolidate the data in a summary sheet.

[Fibon]

its got to be someone sorting incorrectly.
shared workbooks can be a nightmare, ban them from sorting,changing fonts,colors,sizes anything at all if they want to do that make them take a copy do what they need there then update the master.
check the workbook daily and make sure every one has saved and exited.
make sure the same user is not listed multiple times as having the workbook open.

Thank, Martin, we do suspect that the issue may have arose due to sorting or filtering but we have ruled out sorting incorrectly. We aware that sometime sorting can be just on the active section if the spreadsheet is broken by empty rows or columns but we have ruled out this possibility.

On the other hand, we are not sure what gone wrong.

Frequently we have found that when a user enters an entry in a particular row (Job Number) and saved it, after a while, the data went to another row (the row with a different Job Number). It may happen in the same days or after a few days. When we tracked the changes using the "Track Changes" button on the Excel, the history actually highlighted yet another row where the data were been entered!!

- Say we have 3 rows with Job Number A1001, A1002 & A1003
- A user key in the job to row with Job Number A1002
- The next day, the entries mysteriously went to row with Job Number A1001 and the job at Job Number A1001 went to yet another row.
- When we tracked the history, the history highlighted the cells in row with Job Number A1003 and said that the user had actually entered the data into this row. Worst, the cells is row with Job Number A1003 were still blank!

You have mentioned that "make sure the same user is not listed multiple times as having the workbook open". When we checked who were logging in by clicking "Share Workbook" under "Review", we actually found multiple simultaneous log-in by same users. Shall we delete the older log-in?

[Fibon]

hello

can they edit other "column" not supposed to be their column for data entry? As martin pointing to.
if that's the case..
you can control which user can edit certain ranges using Tools > Protection > Allow users to edit ranges

or drastic move
"just a suggestion..."

You could use a different sheet (chop off the original every column) for each user and have different passwords on each sheet.
Then consolidate the data in a summary sheet.

Yes & No.

Yes, we do use Protection and Passwords to limit different users to edit different section of the spreadsheet.
and I say "No" because, we generally divide the spreadsheet into few major section, for example a section for "Sales", another section for "Production" etc.
Different sections will have different password but each sections, there will be multiple users editing it. For example, under the "sales" section, few sales rep will be updating new jobs into the spreadsheet.

[martindwilson]

that's probably what causes your problem ,the workbook gets bloated and corrupted its trying to save conflicting views for everyone. if its a busy workbook you should make sure everyone saves and exits workbook daily,and saves their own copy in case things go wrong. while they are all out of the book check for people seemingly still logged in, just unshare/reshare the workbook to remove them all (you can remove them individually but resetting the sharing is the aim here).
then get everyone to access it again and quickly check all their data is there.
check the size of the workbook does it seem too big for whats actually in it compared with an unshared workbook of similar layout. it may be worth unsharing and saving the master as something else to see if size reduces then rename .
or even run http://www.microsoft.com/en-us/downl....aspx?id=21649 in it(its probably worth installing this add in as its quick for future use)

[Fibon]

Thank you so much Martin. Seem like we will never be able to get to the source of the problem but to avoid the frustration, most probably the best advice is to follow what you have mentioned:

1) Everyone save and exit the workbook daily; save a separate copy of their own in case something went wrong;
2) Unshare and reshare the workbook as frequent as possible;
3) Check the integrity of the data frequently and if something is not right, we always have the back-up copy to fall back to.

Fibon