Finding the Intersection of External Defined Ranges

 Last reviewed: November 3, 1994
Article ID: Q75960
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, versions 2.2, 2.21, 3.0

SUMMARY

In Microsoft Excel, a space acts as the intersection operator. When you enter a space between two defined names, the intersection of those named ranges is returned. When you have two variable names entered on a worksheet, and those names refer to defined ranges on a second worksheet, you must use the following formula to find the intersection of the variable names

Microsoft Excel version 5.0

   =INDIRECT("[BOOK1.XLS]SHEET1!"&A1) INDIRECT("BOOK1.XLS]SHEET1!"&B1)
where BOOK1.XLS refers to the name of the workbook, SHEET1 refers to the name of the sheet within the workbook where the names are defined, and A1 and B1 are the cells on the current worksheet that contain the variable names.

Microsoft Excel versions 2.x, 3.0 and 4.0

   =INDIRECT("SHEETNAME.XLS!"&A1) INDIRECT("SHEETNAME.XLS!"&B1)
where SHEETNAME.XLS is the name of the worksheet where the names are defined, and A1 and B1 are the cells on the current worksheet that contain the variable names.

MORE INFORMATION

Example

The following example returns the value at the intersection of two defined ranges, "Joe" and "Weight", which have been defined on another sheet.

  1. Enter the following values on a new worksheet: 

          A1:             B1: Joe         C1: Carl
      A2: Height      B2: 120         C2: 130
      A3: Weight      B3: 180         C3: 150

  2. Select cells A1:C3.

Microsoft Excel version 5.0

  1. From the Insert menu, choose Name, and then choose Create. Choose OK.

  2. From the File menu, choose Save As. In the File Name box, type "Book1" (without the quotation marks). Choose OK.

  3. In a new workbook, on a new worksheet, enter the following: 

       A1: Joe                 B1: Weight
   A2: =INDIRECT("[book1.xls]Sheet1!"&A1) INDIRECT("[book1.xls]Sheet1!"&B1)

Microsoft Excel versions 2.x, 3.0 and 4.0

  1. From the Formula menu, choose Create Names. Choose OK.

  2. From the File menu, choose Save As. In the File Name box, type "Sheet1.xls" (without the quotation marks). Choose OK.

  3. On a new worksheet, enter the following: 

          A1: Joe                 B1: Weight
      A2: =INDIRECT("Sheet1.XLS!"&A1) INDIRECT("Sheet1.XLS!"&B1)
In this example, the value returned in cell A2 is 180.

Cells A1 and B1 contain the variable names that refer to defined ranges on SHEET1.XLS. Cell A2 returns the intersection of those ranges.

REFERENCES

"Microsoft Excel User's Guide, Book 1," version 4.0, page 271.

"Microsoft Excel User's Guide," version 3.0, page 231.

KBCategory: kbusage
KBSubcategory:

Additional reference words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0
3.00 4.0 4.00 5.00

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: November 3, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.