Aanmelden met Microsoft
Meld u aan of maak een account.
Hallo,
Selecteer een ander account.
U hebt meerdere accounts
Kies het account waarmee u zich wilt aanmelden.
Engels
Dit artikel is niet beschikbaar in uw taal.

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.

Meer hulp nodig?

Meer opties?

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 deze informatie nuttig?

Wat heeft uw ervaring beïnvloed?
Als u op Verzenden klikt, wordt uw feedback gebruikt om producten en services van Microsoft te verbeteren. Uw IT-beheerder kan deze gegevens verzamelen. Privacyverklaring.

Hartelijk dank voor uw feedback.

×