Creating Multisheet Names | Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Names can extend into the third dimension; in other words, they can extend across multiple worksheets in a workbook. You can't simply select the multisheet range and enter a name in the Name box, however. You must use the New Name dialog box to create a multi- sheet name. The format for a multisheet reference looks like this:

 FirstSheet:LastSheet!RangeReference

In Figure 3-8, a multisheet name (DataCube), defined for A1:C3, extends across Sheet1, Sheet2, and Sheet3.

image from book
Figure 3-8: Create a multisheet name.

You can, of course, simply type the multisheet range reference into the Refers To field. If you want to create the name by pointing to the range, though, you'll find it a bit tricky. Even if you begin by selecting a multisheet range, Excel does not use this selected range address in the New Name dialog box.

Follow this step-by-step procedure to create a name called DataCube that refers to the range A1:C3 across three worksheets (Sheet1, Sheet2, and Sheet3):

Activate Sheet1.
Choose Formulas Defined Names Define Name to display the New Name dialog box.
Type DataCube in the Name field.
Highlight the range reference in the Refers To field, and press Delete to delete the range reference.
Select the range A1:C3 in Sheet1. The following appears in the Refers To field:
```
 =Sheet1!$A$1:$C$3 
```
Press Shift and then click the Sheet tab for Sheet3. You'll find that Excel inexplicably changes the range reference to a single cell. At this point, the following appears in the Refers To field:
```
 ='Sheet1:Sheet3'!$A$1 
```
Reselect the range A1:C3 in Sheet1 (which is still the active sheet). The following appears in the Refers To field:
```
 ='Sheet1:Sheet3'!$A$1:$C$3 
```
Because the Refers To field now has the correct multisheet range address, click OK to close the Define Name dialog box.

After you define the name, you can use it in your formulas. For example, the following formula returns the sum of the values in the range named DataCube.

 =SUM(DataCube)

Note

Multisheet names do not appear in the Name box or in the Go To dialog box (which appears when you choose Home Editing Find & Select & Go To). In other words, Excel enables you to define the name, but it doesn't give you a way to automatically select the cells to which the name refers.

If you insert a new worksheet into a workbook that uses multisheet names, the multisheet names will include the new worksheet-as long as the sheet resides between the first and last sheet in the name's definition. In the preceding example, a worksheet inserted between Sheet1 and Sheet2 will be included in the DataCube range. However, a worksheet inserted before Sheet1 or after Sheet 3 will not be included.

If you delete the first or last sheet included in a multisheet name, Excel changes the name's range in the Refers To field automatically. In the preceding example, deleting Sheet1 causes the Refers To range of DataCube to change to

 ='Sheet2:Sheet3'!$A$1:$C$3

Multisheet names should always be workbook-level in scope. Multisheet names that are worksheet-level will work properly but will display an error in the Name Manager dialog.