Troubleshoot shared workbooks

Microsoft Office Excel 2003

previous page next page

You can access the following features only if you stop sharing the workbook.

You cannot use shared workbooks on Web servers.

Unavailable feature	Alternatives
Insert or delete blocks of cells	You can insert entire rows and columns.
Delete worksheets	None
Merge cells or split merged cells	None
Add or change conditional formats	Existing conditional formats continue to appear as cell values change, but you can't change these formats or redefine the conditions.
Add or change data validation	Cells continue to be validated when you type new values, but you can't change existing data validation settings.
Create or change charts or PivotChart reports	You can view existing charts and reports.
Insert or change pictures or other objects	You can view existing pictures and objects.
Insert or change hyperlinks	Existing hyperlinks continue to work.
Use drawing tools	You can view existing drawings and graphics.
Assign, change, or remove passwords	Existing passwords remain in effect.
Protect or unprotect worksheets or the workbook	Existing protection remains in effect.
Create, change, or view scenarios	None
Group or outline data	You can continue to use existing outlines.
Insert automatic subtotals	You can view existing subtotals.
Create data tables	You can view existing data tables.
Create or change PivotTable reports	You can view existing reports.
Write, record, change, view, or assign macros	You can run existing macros that don't access unavailable features. You can record shared workbook operations into a macro stored in another nonshared workbook.
Add or change Microsoft Excel 4 dialog sheets	None
Change or delete array formulas	Existing array formulas continue to calculate correctly.

Show The links in my workbook don't work.

Update links that have #REF errors If another user creates and saves a new link in a shared workbook while you are editing the same workbook, and you save or update the workbook, you get the #REF error .

On the Edit menu, click Links.
Click the link with the error.
To clear the #REF error and display the linked data, click Update Link.

Check that link sources are correct If you copied or moved the shared workbook from its original location to your network, or if the source workbook for the links isn't stored in a network location, you may see a message that the links cannot be updated. You can repair the links as follows:

If you copied the workbook, open the original version of the shared workbook on your local system, and then use the Save As command (File menu) to save the shared workbook on the network location. This command updates the links for the new location.
If you moved the shared workbook, don't have the original version, or Save As didn't repair the links, make sure the source workbook for the links is stored on a network location, and then fix the links.
How?
1. On the Edit menu, click Links.
  The Links command is unavailable if your file does not contain linked information.
2. Click Check Status to update the status for all links in the list. This may take a while if there are a lot of links, or if the source workbook for the links is on a network location, and the network is slow.
3. Check the status in the Status column, select the link, and then take the action needed.
  
  OK    No action required, the link is working and up to date.
  
  Unknown Click Check Status to update the status for all links in the list.
  
  Not applicable    The link uses Object Linking and Embedding (OLE) or Dynamic Data Exchange (DDE). Microsoft Excel cannot check the status of these types of links.
  Error: Source not found    Click Change Source, and select another workbook.
  Error: Worksheet not found    Click Change Source, and then select another worksheet. The source may have been moved or renamed.
  Warning: Values not updated    Click Update Values. The link was not updated when the workbook was opened.
  Warning  Click Open Source, and calculate the workbook by pressing F9. The workbook may be set to manual calculation. To set to automatic calculation, on the Tools menu, click Options, select the Calculation tab, and then click Automatic.
  
  Warning  Some names cannot be resolved until the source workbook is opened. Click Open Source, switch back to the destination workbook, and click Check Status. If this does not resolve the problem, make sure the name is not misspelled or missing. Switch to the source workbook, and then on the Insert menu, point to Name, and then click Define, and look for the name.
  
  Warning  Click Open Source. The link cannot be updated until the source is open.
  
  Source is open    The source is open. No action required unless worksheet errors are present.
  
  Values updated from filename    No action required, the values have been updated.
  
  Warning  Excel cannot determine the status of the link. The source may contain no worksheets, or be saved in an unsupported file format. Click Update Values.

Show The Protect Workbook command isn't available or I can't type in the Password box.

You workbook is probably a shared workbook, you cannot protect it or assign a password. If you want to protect a shared workbook, first stop sharing it.

Show How?

Have all other users save and close the shared workbook. If other users are editing, they will lose any unsaved work.
Unsharing the workbook deletes the change history. If you want to keep a copy of this information, print out the History worksheet or copy it to another workbook.
How?
1. On the Tools menu, point to Track Changes, and then click Highlight Changes.
2. In the When box, click All.
3. Clear the Who and Where check boxes.
4. Select the List changes on a new sheet check box, and then click OK.
5. Do one or more of the following:
  - To print the History worksheet, click Print .
  - To copy the history to another workbook, select the cells you want to copy, click Copy , switch to another workbook, click where you want the copy to go, and click Paste .
  Note You may also want to save or print the current version of the workbook, because this history might not apply to later versions. For example, cell locations, including row numbers, in the copied history may no longer be current.
On the Tools menu, click Share Workbook, and then click the Editing tab.
Make sure that you are the only person listed in the Who has this workbook open now box.
Clear the Allow changes by more than one user at the same time check box.

If this check box is not available, you must unprotect the workbook before clearing the check box.
How?
1. Click OK, point to Protection on the Tools menu, and then click Unprotect Shared Workbook.
2. Enter the password if prompted, and then click OK.
3. On the Tools menu, click Share Workbook, and then click the Editing tab.
When prompted about the effects on other users, click Yes.

You can then protect the workbook and assign a password if you want.

Show How?

Protect worksheet elements

Show Protect worksheet elements from all users

Switch to the worksheet you want to protect.
Unlock any cells you want users to be able to change: select each cell or range, click Cells on the Format menu, click the Protection tab, and then clear the Locked check box.
Hide any formulas that you don't want to be visible: select the cells with the formulas, click Cells on the Format menu, click the Protection tab, and then select the Hidden check box.
Unlock any graphic objects you want users to be able to change.
How?
You don't need to unlock buttons or controls for users to be able to click and use them. You can unlock embedded charts, text boxes, and other objects created with the drawing tools that you want users to be able to modify. To see which elements on a worksheet are graphic objects, click Go To on the Edit menu, click Special, and then click Objects.
1. Hold down CTRL and click each object that you want to unlock.
2. On the Format menu, click the command for the object you selected: AutoShape, Object, Text Box, Picture, Control, or WordArt.
3. Click the Protection tab.
4. Clear the Locked check box, and if present, clear the Lock text check box.
On the Tools menu, point to Protection, and then click Protect Sheet.
Type a password for the sheet.

Note The password is optional; however, if you don't supply a password, any user will be able to unprotect the sheet and change the protected elements. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.
In the Allow all users of this worksheet to list, select the elements that you want users to be able to change.
Click OK, and if prompted retype the password.

Show Give specific users access to protected ranges

You must have Windows 2000 to give specific users access to ranges.

On the Tools menu, point to Protection, and then click Allow Users to Edit Ranges. (This command is available only when the worksheet is not protected.)
Click New.
In the Title box, type a title for the range you're granting access to.
In the Refers to cells box, type an equal sign (=), and then type a reference or select the range.
In the Range password box, type a password to access the range.

The password is optional; if you don't supply a password, any user will be able to edit the cells.
Click Permissions, and then click Add.
Locate and select the users to whom you want to grant access. If you want to select multiple users, hold down CTRL while you click the names.
Click OK twice, and if prompted retype the password.
Repeat the previous steps for each range for which you're granting access.
To retain a separate record of the ranges and users, select the Paste permissions information into a new workbook check box.
Protect the worksheet: On the Tools menu, point to Protection, click Protect Sheet, make sure the Protect worksheet and contents of locked cells check box is selected, type a password for the worksheet, click OK, and retype the password to confirm.

Note A sheet password is required to prevent other users from being able to edit your designated ranges. Make sure you choose a password you can remember, because if you lose the password, you cannot gain access to the protected elements on the worksheet.

Help protect workbook elements and files

Show Protect workbook elements

On the Tools menu, point to Protection, and then click Protect Workbook.
Do one or more of the following:
- To protect the structure of a workbook so that worksheets in the workbook can't be moved, deleted, hidden, unhidden, or renamed, and new worksheets can't be inserted, select the Structure check box.
- To use windows of the same size and position each time the workbook is opened, select the Windows check box.
- To prevent others from removing workbook protection, type a password, click OK, and then retype the password to confirm it.

Show Protect a shared workbook

If the workbook is already shared, and you want to assign a password to protect the sharing, unshare the workbook.
How?
1. Have all other users save and close the shared workbook. If other users are editing, they will lose any unsaved work.
2. Unsharing the workbook deletes the change history. If you want to keep a copy of this information, print out the History worksheet or copy it to another workbook.
  How?
  
  On the Tools menu, point to Track Changes, and then click Highlight Changes.
  
  In the When box, click All.
  
  Clear the Who and Where check boxes.
  
  Select the List changes on a new sheet check box, and then click OK.
  
  Do one or more of the following:
  
  To print the History worksheet, click Print .
  
  To copy the history to another workbook, select the cells you want to copy, click Copy , switch to another workbook, click where you want the copy to go, and click Paste .
  
  Note You may also want to save or print the current version of the workbook, because this history might not apply to later versions. For example, cell locations, including row numbers, in the copied history may no longer be current.
3. On the Tools menu, click Share Workbook, and then click the Editing tab.
4. Make sure that you are the only person listed in the Who has this workbook open now box.
5. Clear the Allow changes by more than one user at the same time check box.
  
  If this check box is not available, you must unprotect the workbook before clearing the check box.
  How?
  
  Click OK, point to Protection on the Tools menu, and then click Unprotect Shared Workbook.
  
  Enter the password if prompted, and then click OK.
  
  On the Tools menu, click Share Workbook, and then click the Editing tab.
6. When prompted about the effects on other users, click Yes.
Set other types of protection if you want: Give specific users access to ranges, protect worksheets, protect workbook elements, and set passwords for viewing and editing.
On the Tools menu, point to Protection, and then click Protect Shared Workbook or Protect and Share Workbook.
Select the Sharing with track changes check box.
If you want to require other users to supply a password to turn off the change history or remove the workbook from shared use, type the password in the Password box, and then retype the password when prompted.
If prompted, save the workbook.

Show Protect a workbook file from viewing or editing

On the File menu, click Save As.
On the Tools menu, click General Options.
Do either or both of the following:
- If you want users to enter a password before they can view the workbook, type a password in the Password to open box, and then click OK.
- If you want users to enter a password before they can save changes to the workbook, type a password in the Password to modify box, and then click OK.
When prompted, retype your passwords to confirm them.
Click Save.
If prompted, click Yes to replace the existing workbook.

Then use the Share Workbook command (Tools menu) to return the workbook to shared use.

Note that protecting a workbook is different from protecting sharing and the change history. When you protect a workbook by using the Protect Workbook command (Tools menu, Protection submenu), you must share the workbook after you assign the password. When you assign a password by clicking the Sharing with Track Changes option (Tools menu, Protection submenu, Protect and Share Workbook command), the workbook is shared automatically.

If the workbook is already shared, when you point to Protection on the Tools menu, click Protect Shared Workbook, and select the Sharing with Track Changes check box, you cannot assign a password for this protection. To assign this password, you must first unshare the workbook.

Show The Allow changes by more than one user at the same time check box on the Share Workbook dialog box is not available.

Click OK, point to Protection on the Tools menu, and then click Unprotect Shared Workbook.
Enter the password if prompted, and then click OK.
On the Tools menu, click Share Workbook, and then click the Editing tab.

Data or files are gone or inaccessible

Show I can only get read-only access.

Is the workbook a Microsoft Excel 95 shared list? If so, Excel always opens the file read-only. To convert the file to an Excel shared workbook:

On the File menu, click Save As.
In the Save as type box, click Microsoft Excel Workbook (*.xls).
Type a new name in the File name box, and then click Save.
Share the workbook: on the Tools menu, click Share Workbook, click the Editing tab, select the Allow changes by more than one user at the same time check box, click OK, and save the shared workbook.

Is read-only recommended? Someone may have selected this option in the Save Options dialog box. To open the file read-write, click No in response to the message suggesting that the file should be opened read-only.

Save a personal copy that you can edit The workbook file may be read-only, or you may not have permissions for the location where it's stored. To edit and save the data in the file, click Save As on the File menu, and then save the shared workbook with a new name on your local disk.

Show Another user saved over my data or edits.

Retrieve data from the change history If the change history is being maintained back to the time when your change was overwritten, you can find the data that you originally entered on the History worksheet, and copy it from there to its original location.

Show How?

On the Tools menu, point to Track Changes, and then click Highlight Changes.
In the When box, click All.
Clear the Who and Where check boxes.
Select the List changes on a new sheet check box, and then click OK.
Find your deleted data and click Copy on the Standard toolbar.
Switch to the original worksheet, click where the data belongs, and click Paste .

Save a copy of your changes To keep a copy that can't be overwritten, click Save As on the File menu and save an extra copy of the shared workbook on your local disk.

Show A message says I'm no longer connected.

Another user may have stopped sharing the workbook If sharing was stopped while you were editing, you'll see the message even after the workbook is reshared, and changes from your current editing session will have to be re-entered. Close the workbook and follow up with the owner or other users.

You may have been disconnected Your connection to the network share may have become broken, disconnecting you from the workbook, or another user may have disconnected you. If the workbook is still shared and available, you have two choices:

You can reconnect but lose current changes You can open the shared workbook again, but any unsaved changes you made in your current editing session will be lost.
You can save and merge in your changes You can preserve your unsaved work and then merge it back into the shared workbook.
How?
1. Click OK in response to the message telling you that you are no longer connected.
2. On the File menu, click Save As.
3. In the File name box, type a new name for the workbook, and then click Save.
4. On the File menu, click Close.
5. Open the original shared workbook.
6. On the Tools menu, click Compare and Merge Workbooks.
7. Click the file you saved in step 3 to merge in your changes.

previous page start next page