Assigning range names that span different sheets
The only time that sheet names are really required as part of the range name is when the cell range it refers to spans different sheets of the workbook. In order to name these so-called 3-D references (that is, the same cell range that spans multiple adjacent worksheets), you need to specify the different worksheets involved.
The easiest way to do this is by specifying the sheets in the Define Name dialog box as follows:
- Make the first worksheet included in the 3-D reference active.
- Choose Insert → Name → Define to open the Define Name dialog box.
- In the Names in Workbook text box, type the descriptive name for the range that spans more than one sheet.
- Press the Tab key until the Refers To text box is selected and then type = (equal sign).
- Select the tab of the active sheet and then hold down the Shift key as you select the tab of the last worksheet to be included.
When you select the tab of the active sheet, Excel inserts its sheet reference in the Refers To text box. When you Shift+click the tab of the last sheet in the 3-D reference, Excel inserts its sheet reference after that of the active sheet - separated by a colon. - Select the range of cells in the active sheet to be included in all the sheets in the 3-D reference.
As you drag through the cells in the active worksheet, Excel automatically collapses the Define Name dialog box so that you can see what you're doing. As you select the cell range, Excel inserts its range reference (using absolute cell references) after the sheet range. - Click OK to close the Define Name dialog box.
After naming a 3-D reference, you can use its range name in formulas instead of having to go to the trouble of manually selecting the individual cell range in each sheet. This is a real timesaver when building formulas that accumulate values from different sheets.
