Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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!A1Note 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.

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×