You receive an error message when you save a workbook, define a name, delete a name, or use a defined name in a formula in Excel 2002 or 2003 (292471)


The information in this article applies to:

  • Microsoft Office Excel 2003
  • Microsoft Excel 2002
This article was previously published under Q292471

SYMPTOMS

When you attempt to save a Microsoft Excel workbook, define a name, delete a name, or use a defined name in a formula, you may receive the following error message:
Excel cannot complete this task with the available resources. Choose less data or close other applications.

CAUSE

This behavior can occur if you define names that indirectly refer to other nested names that are more than 20 levels deep. When you add or use a name that exceeds that level of indirection in a formula, you receive this error message. You also receive this error message when you delete a name that is referenced by more than 20 levels of defined names.

RESOLUTION

To resolve this issue, change the defined names so that they reference the given cells more directly.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

A level of indirection in defined names is created when one name refers to another name or to a cell reference.

If you define the following names in a workbook named Book1 
   Name    Refers to       Level of indirection
   --------------------------------------------

   Test1   =Sheet1!$A$1               1
   Test2   =Book1!Test1               2
   Test3   =Book1!Test2               3
   Test4   =Book1!Test3               4
   Test5   =Book1!Test4               5
   Test6   =Book1!Test5               6
   Test7   =Book1!Test6               7
   Test8   =Book1!Test7               8
   Test9   =Book1!Test8               9
   Test10  =Book1!Test9               10
   Test11  =Book1!Test10              11
   Test12  =Book1!Test11              12
   Test13  =Book1!Test12              13
   Test14  =Book1!Test13              14
   Test15  =Book1!Test14              15
   Test16  =Book1!Test15              16
   Test17  =Book1!Test16              17
   Test18  =Book1!Test17              18
   Test19  =Book1!Test18              19
   Test20  =Book1!Test19              20 (the limit)
   Test21  =Book1!Test20              21
where each line represents one level of indirection, and you attempt to use a name that exceeds the twentieth level of indirection, you receive the error message described in the "Symptoms" section.

You may also receive this error message if you try to delete a name that has too many levels of indirection below the name. For example, if you try to delete the name "Test1", you may receive the error message because the name "Test21" is 20 or more levels of indirection away.
Modification Type:MinorLast Reviewed:8/27/2004
Keywords:kbprb kbsavefile kberrmsg kbpending KB292471
©2004 Microsoft Corporation. All rights reserved.