 |
|
|
|
|
Download the associated Workbook for this lesson
Preparing a Workbook for Merging
As mentioned above it is possible to Merge multiple copies of the same Workbook if it is Shared. But before this is possible we must have specified a number of days in the Keep change history for…..days option on the Advanced page tab of the Share Workbook dialog box. This means that if we created our Shared Workbook on the 1-Jan-2005 and had set the Keep change history for…..days to 15 days, we MUST perform the Merge before the 16-Jan-2005 (15 days from 1-Jan-2005). After this date it is no longer possible to Merge the Workbooks. If you are unsure how long it will be before you Merge the copies, then simply enter a large number of days. The Maximum allowed days are 32,500.
In a nutshell, the procedure for preparing a Shared Workbook for Merging would be:
Go to Tools>Share Workbook and ensure the Allow changes by more than one user at the same time checkbox is checked.
Select the Advanced page tab
Check the Keep change history for checkbox and type in any number of days. Remember though that if the number of days you specify expires you will not be able to perform a Merge.
Click OK and save.
To allow distribution of the Workbook to multiple users go to File>Save as and save multiple copies of the Workbook, each with a different name.
Merging the Workbooks
Once the Workbook has been shared for the required time (before Keep change history for… days expires) we can Merge all copies of the Shared Workbook. This is a very straightforward process, all we need to do is ensure all copies we want to Merge are closed. The only copy open should be the Workbook we are going to perform the Merge in.
Go to Tools>Merge Workbooks. If prompted to save, do so.
Select the Workbook(s) to Merge from the Select Files to Merge Into the Current Workbook dialog box. For more than one copy hold down the Ctrl or Shift key while selecting. Do not include the current Workbook!
Click OK.
Excel Help
If a cell contains a comment, the comment includes the name of the person who inserted the comment. When you merge shared workbooks and a cell has comments from more than one person, the comments appear one after another in the comment box for the cell.
End of Help
All copies of the Shared Workbook will now be Merged into one. If you wish you can now go to Tools>Track Changes>Accept or Reject Changes and use the Accept or Reject Changes dialog to overwrite, accept or reject any changes.
Removing a Workbook from Shared Use
When we remove a Workbook from shared use you will be disconnecting all other users of the Shared Workbook and they will lose any unsaved Work. It also means we will be switching off the Change History and erasing any stored history. To top this off it also means the Workbook cannot be Merged with other copies of the Workbook. For these reasons you should be certain that all needed work is completed, then inform all other users of the Shared Workbook of your intentions so they do not lose any unsaved Work.
Ideally the Workbook should only be removed from Shared use when all work has been competed. The Steps for removing a Workbook from Shared use are as below:
Go to Tools>Shared Workbook and select the Editing tab.
Look in the Who has this workbook open now box. If others do have the Workbook open they will lose unsaved work.
Uncheck the Allow changes by more than one user at the same time checkbox.
Click OK
Excel will prompt you about the effects this will have on other users.
Click Yes.
Now we have covered Sharing Workbooks and Merging Workbooks etc, we can divert back to where we left of ie; Protecting Shared Workbooks
When we apply protection to a shared Workbook we are doing so so other users of the Shared Workbook cannot intentionally or accidentally remove Track Changes, Unshare the Workbook or change the Keep change history for…..days option on the Advanced page tab of the Share Workbook dialog box. If no password is provided any user can increase the number of days the History will be retained for but not reduce the number.
As strange as it sounds, we cannot apply a Password to a Shared Workbook, we must first remove the Workbook from Shared Use (see Removing a Workbook from Shared Use above), which can cause problems as stated above. Ideally, we should decide whether we will want to password protect a Shared Workbook before we distribute it for sharing. We can however apply protection to a Shared Workbook if no password is required. The steps for protecting a Shared Workbook are:
If the Workbook is not yet a Shared Workbook go to Tools>Share Workbook and select the Editing page tab. Check the Allow changes by more than one user at the same time checkbox and then select the Advanced page tab. Ensure that the Keep change history for option is selected and you have specified a number of days, remembering that if the number of days you specify expires you will not be able to perform a Merge. Go back to the Editing page tab and uncheck the Allow changes by more than one user at the same time checkbox, then click OK.
If the workbook is already Shared, go to Tools>Share Workbook and select the Advanced tab. Ensure that the Keep change history for option is selected and you have specified a number of days, remembering that if the number of days you specify expires you will not be able to perform a Merge.
Click OK
Go to Tools>Protection>Protect Shared Workbook or Protect and Share Workbook if not yet Shared.
Check the Sharing with track changes checkbox.
If the Workbook is not currently shared you will be able to type in a password. If this option is not enabled and you want to apply a password, you will need to remove the Workbook from Shared use!
Click OK and say Yes if prompted to save.
Excel Help
Note: When you again want to allow changes without tracking them, point to Protection on the Tools menu, and then click Unprotect Shared Workbook. If prompted, enter the password. When you remove password protection from the sharing, you also remove the workbook from shared use.
End of Help
The last method of Protection is accessed via File>Save as options. It is from within here we can set the File sharing options.
This is where we can type a password of up to 15 characters which will prevent another user from being able to open the Workbook, unless they have the password.
By applying a password in this box we are preventing other users from making any changes to our Workbook. Another user will be able to open the Workbook and make changes, but they will not be able to save the Workbook with the same name
Checking this checkbox will mean the user will be asked to open the Workbook as read-only, unless they need to make changes to it. They will have three choices Yes, No and Cancel. If they select Yes (default) they will not be able to save the Workbook with the same name. If they select No the Workbook will open as normal and they will be able to save the Workbook as normal. Selecting Cancel will stop the Workbook from opening.