Microsoft Knowledge Base

The information in this article applies to:

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In the versions of Microsoft Excel mentioned above, when you use a reference for the same local name across multiple worksheets in the same workbook, the formula that contains the reference returns a #NAME? error if the reference is in the following form:

   Sheet1:Sheet2!Sales

   =SUM(Sheet1:Sheet2!Sales)

CAUSE

This error occurs because Microsoft Excel does not support referencing names across sheets in a workbook using a 3-D reference as it is used in the above example. You can reference a range of cells across multiple sheets in a workbook using the cell reference A1, B3, and so on. For example, the following reference refers to the range A2:A5 on Sheet1, Sheet2, and Sheet3:

   =Sheet1:Sheet3!A2:A5

WORKAROUNDS

To reference a range on multiple worksheets in a workbook, you can use any of the following methods.

Create a Global Reference

To create a reference that refers to the sales information contained on multiple worksheets in the workbook and use this reference in a formula, follow the appropriate procedure below.

• Create the global name Sales that refers to all of the sales information. If the sales information is contained in cell A1 on each of the worksheets, global Sales refers to:

        =Sheet1:Sheet3!$A$1
  

• If the sales information is not contained in the same range on each worksheet, define the global Sales as follows:

        =Sheet1!$A$1, Sheet2!$A$4, Sheet3!$B$3
  

• To sum the local sales information on each worksheet, use the following formula:

        =SUM(Sales)
  

• To sum the local sales information on each worksheet without defining a global name, use the following formula if the sales information is contained in cell A1 on each of the worksheets:

        =SUM(Sheet1:Sheet3!$A$1)
  

• If the sales information is not contained in the same range on each of the worksheets, use the following formula

        =SUM(Sheet1!$A$1+Sheet2!$A$4+Sheet3!$B$3)
  

Use Data Consolidation

You can also use the Consolidate feature to summarize your data using different functions such as SUM, MIN, and VAR. For more information about Consolidating Data By Position, choose the Search button in Help and type the following:

   Consolidating data

User-Defined Function

You can use the following user-defined function to sum all the occurrences of a given local name in the active workbook.

NOTE: The local name must refer to a single cell.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

   ' This user defined function searches the list
   ' of names for local names that match the Local_Name
   ' argument.  When it finds a matching name, it evaluates
   ' the name and keeps a running total of all the matches.
   ' If it does not find a match, it returns the #NAME?
   ' error value.
   ' Example of use:  = SUMLocals("Sales")

   Function SumLocals(Local_Name)
   Dim NameCounter As Integer, Match As Boolean

   'Initializes NameCounter
   NameCounter = 1
   With ActiveWorkbook
      'The macro will loop through the names until
      'it runs out of names (NameCounter = .Names.Count).
      Do While NameCounter <= .Names.Count
         'Checks to see if it is a local or global name.
         'Local names contain a "!".
         If InStr(.Names(NameCounter).Name, "!") > 0 Then

            'Checks to see if the name matches.
            If Right(.Names(NameCounter).Name, Len(Local_Name)) = _
               Local_Name Then

               'Keeps a running total.
               SumLocals = SumLocals + _
                  Evaluate(.Names(NameCounter).Value)

               'Sets the Match to true to prevent error message.
               Match = True
            End If   'Name match check

         End If      'Global name check

         'Increments the counter to look at the next name.
         NameCounter = NameCounter + 1
      Loop           'Local name loop
   End With          'ActiveWorkbook

   'If there hasn't been a match, it returns "#NAME?".
   If Match = False Then SumLocals = CVErr(xlErrName)
   End Function

REFERENCES

For more information about Defining A Name With A 3-D Reference, choose the Search button in Help and type:

    3-D references