Symptoms
When you try to share a Microsoft Excel workbook that contains an array formula that is linked to another workbook, you receive the following error message:
Cannot share Workbook Name because it contains array formulas that are linked to other workbooks
Note In Microsoft Office Excel 2007, you can use the following procedure:
To determine the unique number that is associated with the message that you receive, press CTRL+SHIFT+I. The following number appears in the lower-right corner of this message:
101134
Workaround
To work around this issue, define a name for the array of cells in the first workbook. Then, use that name to create a formula that is linked to the second workbook. Alternatively, use a nonarray formula that is linked to the second workbook.
To share a workbook that contains an array formula that is linked to another workbook, follow these steps, as appropriate for the version of Excel that you are running.
Microsoft Office Excel 2007
-
Start Excel.
-
Create a new workbook named Book1, and then type the following data:
A1: 5
A2: 10
A3: 15
A4: 20
A5: 25 -
Click the Formulas tab.
-
In the Defined Names group, click Define Name.
-
In the New Name dialog box, type Sample in the Name box.
-
In the Refers to box, type = Sheet1!$A$1:$A$5, and then click OK.
-
Create a new workbook named Book2, click cell A1, type the following formula, and then press ENTER:
= SUM(Book1!sample)Note The sample name represents the array of cell values.
-
Click the Review tab.
-
Click Share Workbook in the Changes group.
-
Click to select the Allow changes by more than one user at the same time. This also allows workbook merging check box, and then click OK.
-
When you are prompted to save the workbook, click OK, and then save the workbook.
Microsoft Office Excel 2003 and earlier versions of Excel
-
Start Excel.
-
Create a new workbook named Book1, and then type the following data:
A1: 5
A2: 10
A3: 15
A4: 20
A5: 25 -
On the Insert menu, point to Name, and then click Define.
-
In the Define Name dialog box, type Sample in the Names in workbook box.
-
In the Refers to box, type = Sheet1!$A$1:$A$5, and then click OK.
-
Create a new workbook named Book2, click cell A1, type the following formula, and then press ENTER:
= SUM(Book1!sample)Note The sample name represents the array of cell values.
-
On the Tools menu, click Share Workbook.
-
Click to select the Allow changes by more than one user at the same time. This also allows workbook merging check box, and then click OK.
-
When you are prompted to save the workbook, click OK, and then save the workbook.
To share a workbook that contains a nonarray formula that is linked to another workbook, follow these steps:
-
Make sure that Book1 and Book2 are open.
-
In Book2, click cell A1, type the following formula, and the press ENTER:
= [Book1]Sheet1!A1Note Use relative cell reference to point to the cell. Additionally, do not specify the cell range in the formula.
-
Click cell A1, grab the fill handle, and then fill down to complete the rest of the cell values in the array.
-
On the File menu, click Save. Then, close both workbooks.
More Information
Array formulas perform multiple calculations on one or more sets of values. Additionally, array formulas return either a single result or multiple results. Array formulas are enclosed in braces ({}). To enter an array formula, press CTRL+SHIFT+ENTER.