Microsoft KB Archive/906311

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

Article ID: 906311

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Office Excel 2007
 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition

-



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  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.</li> Click the Review tab.</li> Click Share Workbook in the Changes group.</li> 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'''.</li> When you are prompted to save the workbook, click OK, and then save the workbook.</li></ol>

Microsoft Office Excel 2003 and earlier versions of Excel
<ol> Start Excel.</li> Create a new workbook named Book1, and then type the following data:

A1: 5

A2: 10

A3: 15

A4: 20

A5: 25

</li> On the Insert menu, point to Name, and then click Define.</li> In the Define Name dialog box, type in the Names in workbook box.</li> In the Refers to box, type = Sheet1!$A$1:$A$5, and then click OK.</li> 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.</li> On the Tools menu, click Share Workbook.</li> 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'''.</li> When you are prompted to save the workbook, click OK, and then save the workbook.</li></ol>

To share a workbook that contains a nonarray formula that is linked to another workbook, follow these steps: <ol> Make sure that Book1 and Book2 are open.</li> 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.</li> Click cell A1, grab the fill handle, and then fill down to complete the rest of the cell values in the array.</li> <li>On the File menu, click Save. Then, close both workbooks.</li></ol>

<div class="moreinformation_section">

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.

Additional query words: defined names

Keywords: kbofficealertid kbformula kbexpertisebeginner kbxlslink kberrmsg kbtshoot KB906311

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.