Asked by:
Common issues of shared workbook

General discussion
-
Description:
The process works like this: You share a workbook, and as part of sharing, you save the workbook. Someone else then copies the shared workbook and adds their data to the copy. When they're done, you merge the data from the copy back into the original.
General question overview
I have collect and summarize the items from Excel 2013, Excel 2010, Excel 2007, Excel 2003.
In a shared workbook, you cannot
However, this functionality is available
Applis to version
Create an Excel table
None
Excel 2013, Excel 2010, Excel 2007
Insert or delete blocks of cells
You can insert entire rows and columns
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Delete worksheets
None
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Merge cells or split merged cells
None
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Sort or filter by formatting
You can sort or filter by number, text, or date, apply built-in filters, and filter by using the Search box
Excel 2013
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.
Excel 2010, Excel 2007, Excel 2003
Add or change data validation
You can use data validation when you type new values
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Create or change charts or PivotChart reports
You can view existing charts and reports
Excel 2013
Insert or change pictures or other objects
You can view existing pictures and objects
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Insert or change hyperlinks
You can use existing hyperlinks
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Use drawing tools
You can view existing drawings and graphics
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Assign, change, or remove passwords
Assign, change, or remove passwords
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Protect or unprotect worksheets or the workbook
You can use existing protection
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Create, change, or view scenarios
None
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Use the Text to Columns command
None
Excel 2013
Group or outline data
You can use existing outlines
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Insert automatic subtotals
You can view existing subtotals
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Create data tables
You can view existing data tables
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Create or change PivotTable reports
You can view existing reports
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Create or apply slicers
Existing slicers in a workbook are visible after the workbook is shared, but they cannot be changed for standalone slicers or be reapplied to PivotTable data or Cube functions. Any filtering that was applied for the slicer remains intact, whether the slicer is standalone or is used by PivotTable data or Cube functions in the shared workbook.
Excel 2013
Create or modify sparklines
Existing sparklines in a workbook are displayed after the workbook is shared, and will change to reflect updated data. However, you cannot create new sparklines, change their data source, or modify their properties.
Excel 2013
Write, record, change, view, or assign macros
You can run existing macros that don't access unavailable features. You can also record shared workbook operations into a macro stored in another nonshared workbook.
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Add or change Microsoft Excel 4 dialog sheets
None
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Change or delete array formulas
Excel will calculate existing array formulas correctly
Excel 2013, Excel 2010, Excel 2007, Excel 2003
Work with XML data, including:
Import, refresh, and export XML data
Add, rename, or delete XML maps
Map cells to XML elements
Use the XML Source task pane, XML toolbar, or XML commands on the Data menuNone
Excel 2013, Excel 2010, Excel 2007
Use a data form to add new data
You can use a data form to find a record
Excel 2013
Quote from:
http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HA102748977.aspx
http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx
http://office.microsoft.com/en-us/excel-help/use-a-shared-workbook-to-collaborate-HP010096833.aspx
http://office.microsoft.com/en-au/excel-help/troubleshoot-shared-workbooks-HP005203537.aspx
Cause 1 Title: If we get the below error message during used a shared workbook
Filename.xlsx is locked for editing by 'another user'.
Open "Read-Only" or click "Notify" to open read-only and receive notification. when the document is no longer in use.
Description: What makes resolving this particular problem difficult is that there are multiple causes for the problem, and thus multiple solutions
Solution:
- Install the latest service pack
- Install the latest Cumulative Update for Excel
- Turn off the Preview Pane, Details Pane and pop ups that show information about file and desktop items
- Add the OpLocks registry keys
- Install operating system updates
- Use MSConfig to stop third party programs and services, including antivirus software
- Delete the orphaned ~filename.xlsx file from the directory.
- To avoid this problem when you create a workbook that must be shared, turn on the shared workbook option before you save the workbook to a network share.
- If the problem has already occurred, follow these steps:
- Open the shared workbook, and then save it to a local drive.
- Turn on the shared workbook option.
3.Save the workbook on the network share.
Quote from:
Related case:
Cause 2 Title: If we use Excel 2007 shared workbook get the below error message :
The file is no longer shared.
Description:
This problem can occur if the file is saved to a network location and then configured as a shared workbook.
Solution:
To work around this problem, use either of the following methods:
Quote:
http://support.microsoft.com/kb/978382/en-us
Cause 3 Title: If we get the below error message during used a shared workbook
Project is Unviewable
Description: If you try to view a Visual Basic for Applications project in the Microsoft Visual Basic Editor, the following error message may appear, even though you did not lock the project for viewing in the properties for the project. This message may also appear if you click VBAProject Properties on the Tools menu in the Microsoft Visual Basic Editor.
Cause: This behavior occurs if you try to view a project that is in a shared workbook.
Workaround:
You cannot view a project in a shared workbook. If you want to view a project in a shared workbook, turn off sharing. To turn off sharing, use the following steps:
- Open the shared workbook.
- On the Tools menu, click Share Workbook.
Quote:
http://support.microsoft.com/kb/211792/en-us
It also applies to Excel 2010, 2007,2003.
Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.
- Edited by ForumFAQ Monday, October 13, 2014 9:07 AM
Monday, October 13, 2014 9:06 AM
All replies
-
Great post on troubleshooting some problems with shared workbooks. I'd like to add my 2 cts.
I always advise users to not use the shared workbook feature to begin with. Sharing a workbook where multiple people open the same workbook in Excel on their desktop is a very unreliable feature. You are risking loss of the entire workbook due to corruption and edit conflicts may result in unexpected data to be saved.
On the other hand, working together on workbooks using Web Excel (as available in Sharepoint and on OneDrive) is a much more reliable and usable feature, as you will get live update of the spreadsheet and will be able to see what other users are doing in the file while you work on the file.
Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
Monday, October 13, 2014 9:41 AM