You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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

  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.
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.
defined names
Properties

Article ID: 906311 - Last Review: 01/22/2007 23:12:02 - Revision: 2.2

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

  • kbofficealertid kbformula kbexpertisebeginner kbxlslink kberrmsg kbtshoot KB906311
Feedback
;m.content='true';document.getElementsByTagName('head')[0].appendChild(m);" onload="var m=document.createElement('meta');m.name='ms.dqp0';m.content='false';document.getElementsByTagName('head')[0].appendChild(m);" src="http://c1.microsoft.com/c.gif?"> >t=">