Microsoft KB Archive/75960

{|
 * width="100%"|

Finding the Intersection of External Defined Ranges

 * }

-

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(&quot;[BOOK1.XLS]SHEET1!&quot;&A1) INDIRECT(&quot;BOOK1.XLS]SHEET1!&quot;&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(&quot;SHEETNAME.XLS!&quot;&A1) INDIRECT(&quot;SHEETNAME.XLS!&quot;&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.

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

  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  Select cells A1:C3.

Microsoft Excel version 5.0
 From the Insert menu, choose Name, and then choose Create. Choose OK. From the File menu, choose Save As. In the File Name box, type Book1. Choose OK.  In a new workbook, on a new worksheet, enter the following:

  A1: Joe                 B1: Weight A2: =INDIRECT(&quot;[book1.xls]Sheet1!&quot;&A1) INDIRECT(&quot;[book1.xls]Sheet1!&quot;&B1) </li></ol>

Microsoft Excel versions 2.x, 3.0 and 4.0
 From the Formula menu, choose Create Names. Choose OK.</li> From the File menu, choose Save As. In the File Name box, type Sheet1.xls. Choose OK.</li>  On a new worksheet, enter the following:

<pre class="FIXEDTEXT">     A1: Joe                 B1: Weight A2: =INDIRECT(&quot;Sheet1.XLS!&quot;&A1) INDIRECT(&quot;Sheet1.XLS!&quot;&B1) </li></ol>

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.