You cannot share an Excel workbook that contains an array formula that is linked to another workbook

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: 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:

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

1. Start Excel.
2. Create a new workbook named Book1, and then type the following data:
   A1: 5
   A2: 10
   A3: 15
   A4: 20
   A5: 25
3. Click the Formulas tab.
4. In the Defined Names group, click Define Name.
5. In the New Name dialog box, type Sample in the Name box.
6. In the Refers to box, type = Sheet1!$A$1:$A$5, and then click OK.
7. 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.
8. Click the Review tab.
9. Click Share Workbook in the Changes group.
10. 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.
11. When you are prompted to save the workbook, click OK, and then save the workbook.

Microsoft Office Excel 2003 and earlier versions of Excel

1. Start Excel.
2. Create a new workbook named Book1, and then type the following data:
   A1: 5
   A2: 10
   A3: 15
   A4: 20
   A5: 25
3. On the Insert menu, point to Name, and then click Define.
4. In the Define Name dialog box, type Sample in the Names in workbook box.
5. In the Refers to box, type = Sheet1!$A$1:$A$5, and then click OK.
6. 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.
7. On the Tools menu, click Share Workbook.
8. 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.
9. 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:

1. Make sure that Book1 and Book2 are open.
2. In Book2, click cell A1, type the following formula, and the press ENTER:
   = [Book1]Sheet1!A1
   Note Use relative cell reference to point to the cell. Additionally, do not specify the cell range in the formula.
3. Click cell A1, grab the fill handle, and then fill down to complete the rest of the cell values in the array.
4. On the File menu, click Save. Then, close both workbooks.

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.