Problems with sharing a workbook

[*alan*]

We've got a weekly accuracy check spreadsheet that's on another drive and
accessible to me and another co-worker. Normally, we work on it at
different times of the day, but this week we decided to make it accessible
to both of us for simultaneous editing.

The data was first filtered to hide duplicates, then sorted by Employee ID
and Date. I then went to Tools>Share Workbook, checked the box for sharing,
and saved the workbook to our "G" drive.

Then, the trial run: we both accessed it, I had him color-fill the last row
and save. I then saved it on my computer. Sure enough, his change was now
showing on my screen.

BUT ---- the duplicates had re-appeared, and the sorting had all been
undone.

What caused that and what procedure should be followed to make sure that the
initial filtering and sorting stays that way?

Thanks in advance for your help.

 

[Debra Dalgleish]

When you share a workbook, it creates a custom view for each user
(View>Custom Views)
If you select to save Filter settings in your custom view (Tools>Shared
Workbook, Advanced tab) you won't see the other person's applied filters.

That's probably what happened in your workbook.

 

[*alan*]

I just started using Excel about a year ago, and have had no training, so
please forgive me if I appear somewhat dense. I may not have explained the
problem correctly and/or I may not be understanding what you're telling me.
Please bear with my attempt to be clearer and more specific:
- I created a workbook on a separate drive.
- I hid the duplicates. I sorted the data.
- I checked the box to share the workbook.
- I saved it.
- My co-worker accessed the workbook.
- What he saw was exactly what I saw: duplicates hidden, and data sorted.
- He color-filled a row.
- He saved it. So far, no other changes appeared in his view.
- I saved the workbook on my end. I saw his color-filled row.
- I ALSO saw that the duplicates had re-appeared and that the data had
become un-sorted.
- On his end, at that point, the duplicates were still hidden and the data
still sorted.
- But when he saved his workbook again, he was looking at what I was looking
at, i.e. duplicates reappeared and data un-sorted.

If I understand you correctly, it would seem that for the duplicates to stay
hidden and that for the data to remain sorted, the solution is to UN-check
the box for Filter settings on the Advanced tab of the Share Workbook
option?
Somehow, that seems a little counter-intuitive to me, but I'm looking
forward to trying that out at work on Monday --- hope it works.
Thanks for your help, Debra, and please let me know if I've totally
misunderstood your solution.

-- Alan

 

[Debra Dalgleish]

I avoid shared workbooks whenever possible, so I'm just guessing at the
sequence of events that occurs.
My guess is that when you created the shared workbook, it immediately
created a custom view for you. Since the Filter Settings was checked,
the applied filter was part of your custom view.
When your co-worker opened the workbook, he was seeing your custom view.
When he saved the workbook, it created a custom view for him. Since he
hadn't applied the filter, it wasn't part of his custom view, so wasn't
saved.

If you turn off the Filter Settings option, you'll all see the same
filtered data, instead of storing your own settings in your custom view.

 

[*alan*]

Thanks -- I'll give it a try.
-- Alan

"Debra Dalgleish" wrote