21.5. Workbook Sharing
Change tracking is really just one part of a larger feature known as workbook sharing . Workbook sharing makes it possible for more than one person to modify an Excel document at the same time. In order for this sharing to work, the workbook file needs to be placed somewhere that both people can access. (The most typical location is somewhere on a company network.)
Workbook sharing is a little risky (as you'll soon see), but it gives you some unique collaboration abilities that you wouldn't have otherwise . To get some perspective on how workbook sharing works, you must understand what happens when two people fight over a workbook file that isn't shared. Read on.
Ordinarily, only one person can open an Excel workbook file at a time. Excel enforces this restriction to prevent problems that can occur when different people try to make conflicting changes at the same time and someone's changes get lost.
Figure 21-15 shows what happens when you try to open an Excel workbook that someone else is already using. Excel warns you about the problem but gives you the chance to open a copy of the document that you can save with a new name . If you need to update the version that's currently in use, you can ask Excel to notify you when your collaborator closes it and the workbook becomes available again.
Taking turns editing a file works well enough, but it has one significant problemyou never know when the file will become available. If someone opens a workbook and then leaves for a two-week vacation without shutting off the computer, you'll be completely locked out of the workbook. Worst of all, you have no way to know when the workbook will finally be available.
|
And if you make changes to a copy while waiting for someone else (using the same file) to release the original workbook, more problems will occur. The problem is that when you make your changes, you're actually working with a separate copy of the workbook file. If the other person makes any changes to the original workbook file, you'll find yourself in a sticky situation. Excel forces you either to save your copy as a separate file or discard all your hard work before allowing you to open the original copy (see Figure 21-16). There are only two solutionsdiscard your changes completely, or save the workbook with a new name and then open the other person's version.
|
For all of these reasons, it usually makes more sense to use workbook sharing if you know more than one person needs to access or change the same workbook at the same time.
It's quite easy to turn on workbook sharing. In fact, it takes only a couple of steps:
Select Tools Share Workbook.
The Share Workbook dialog box appears.
Turn on the "Allow changes by more than one user at the same time" checkbox.
Select the Advanced tab.
The Advanced tab is shown in Figure 21-17.
|
If you don't want to use change tracking, turn off the checkmark next to the "Don't keep change history" option.
When you share a workbook, Excel assumes you want to use change tracking. That way, if a conflict occurs and one person overwrites another person's changes, you can still find the discarded change in the log if you need it. Otherwise, take this opportunity to turn it off.
In the "Update changes" section, choose how often you want to be informed about changes.
When workbook sharing is switched on, everyone editing the workbook has a separate copy of the workbook. The difference is that each person can save changes to the original workbook file. When you save your copy of the workbook, Excel examines the changes that your collaborators have saved and then refreshes your copy of the workbook accordingly .
Alternatively, you can tell Excel to save your workbook periodically to make sure it stays up to date. To switch on this behavior, change the "When file is saved" option to "Automatically every 15 minutes." (If you want to update more or less frequently, you can change the number of minutes.) Then, select the "Save my changes and see others' changes" option.
Finally, you can choose to refresh your document periodically without actually saving your changes. Simply change the "When file is saved" option to "Automatically every 15 minutes," and select the "Just see other users' changes" option.
If you wish, select one of the settings for "Conflicting changes between users" setting.
Here's your chance to tell Excel what to do if the changes from more than one person conflict with each other. Keeping the standard "Ask me which changes win" setting usually makes sense because Excel explains any conflicts and show you the changes that were made earlier. If you choose "The changes being saved win," Excel automatically overwrites conflicting changes without even warning you that important data could be lost or alerting you that a conflict occurred.
Click OK.
Excel saves the workbook automatically. You can tell that it's shared because the word [Shared] appears in the title bar.
When you're working with a shared workbook, Excel keeps track of how many people are involved. To see who's working on the workbook at any given moment, select Tools Share Workbook. Figure 21-18 shows an example.
|
When workbook sharing is switched on, there's no limit to how many people can open the same workbook at once. However, each person actually has a separate copy of the workbook. Excel automatically synchronizes your copy when you save changes (or at fixed intervals, depending on the "Update changes" option).
This approach works well when people change different portions of the workbook. However, it's entirely possible that two people might choose to edit the same cell at once. Sooner or later, both people will try to save their changes.
For example, imagine Faria changes cell A1 at the same time that Ricardo modifies A1. Faria saves her changes and doesn't experience any problem. Shortly after, Ricardo tries to save his changes. At this point, Excel warns Ricardo that his changes conflict with the changes someone else already made. Ricardo now needs to decide whether to overwrite Faria's changes or discard his own. Figure 21-19 shows an example of the message you might see in this situation.
|
There's a clear problem herethe last person to make changes gets to make decisions about someone else's work. There's no way to avoid limitation of shared workbooks. Excel doesn't provide any way to stop one person from obliterating a co-worker's edits.
In fact, even if changes don't directly conflict, simultaneous edits can still clash . For example, imagine you have two cells (A10 and B10) that don't quite match up the way they should. One person tries to fix the problem by modifying A10. At the same time, another person tries to resolve the same problem by modifying B10. The end result? Both A10 and B10 get changed, Excel doesn't complain, but they still don't match up. Reasons like these make workbook sharing a high-stakes, life-in-the-fast-lane approach.