# Microsoft Knowledge Base

## XL: Using 3-D Local Names Returns #NAME?

Last reviewed: March 27, 1997 Article ID: Q116188
5.00 5.00c 7.00 7.00a | 5.00 5.00a WINDOWS | MACINTOSH kbusage 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

For example, the following returns the #NAME? error:

       =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

However, you cannot use a reference that refers to a named range across multiple sheets in a workbook.

## 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

> KBCategory: kbusage
> KBSubcategory:

> Additional reference words: 5.00 5.00a 5.00c 7.00 7.00a
> Keywords : kbusage
> Version : 5.00 5.00c 7.00 7.00a | 5.00 5.0
> Platform : MACINTOSH WINDOWS

> THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 27, 1997
©1997 Microsoft Corporation. All rights reserved. Legal Notices.