Finding the Intersection of External Defined Ranges

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.

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("[book1.xls]Sheet1!"&A1) INDIRECT("[book1.xls]Sheet1!"&B1)

Microsoft Excel versions 2.x, 3.0 and 4.0

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

From the File menu, choose Save As. In the File Name box, type Sheet1.xls. Choose OK.

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.

Additional query words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 4.0

Keywords :
Version :
Platform :
Issue type :