The Tools, Shared Workbook selection lets you set up options for sharing workbooks on a network. The shared workbook options include the following:
Allow Simultaneous Access to a Workbook
Control How to Track Changes
Control How Changes Get Updated
Control How Conflicting Changes Are Resolved
The first option in the preceding list is found on the Editing tab in the Share Workbook dialog box.
The other options in the list are on the Advanced tab in the Share Workbook dialog box.
The steps in the following To Do exercise show you how to set up shared workbooks. If you're not working on a network, you should be able to set up most of the options successfully and still be able to share workbooks and track changes. You use the My Database workbook for sharing purposes in the exercise. Open the workbook before you begin with step 1.
Save the My Database workbook to a network drive.
Click the Sheet1 tab if it's not already in front. Next, choose Tools, Share Workbook. Excel brings up the Share Workbook dialog box, as shown in Figure 23.1. The Editing tab should be in front.
Choose Allow Changes by More Than One User at the Same Time option. A check mark in the check box means the option is turned on. Now you want to specify keeping a history of the changes for 45 days.
Click the Advanced tab. Figure 23.2 shows the options on the Advanced tab. The options are available in the following sections: Track Changes, Update Changes, and Conflicting Changes Between Users.
Change the number of days to 45. This setting keeps the changes history for 45 days.
Click OK. Excel prompts you to save the workbook.
Click OK to save the workbook. Excel saves the workbook on the network drive if you saved it there, so that others can gain access to the shared workbook. You should see the workbook filename [Shared] in the workbook's title bar at the top of the Excel window.
In the Track Changes section on the Advanced tab in the Share Workbook dialog box, you can control how long to track workbook changes. For example, you might want to track workbook changes for 30 days.
You can also tell Excel how often to update the changes in a shared workbook. In the Update Changes section on the Advanced tab, you can control how changes made by multiple users are consolidated in the main document. You can choose to save the changes whenever the file is saved or in minute-based intervals.
In the Conflicting Changes Between Users section, you can control how conflicting changes are resolved?for instance, when two users change the same cell to different values. Usually, this item should be set to the Ask Me Which Changes Win option.