Referencing Cells or Ranges in Other Worksheets
If your formula needs to refer to a cell in a different worksheet in the same workbook, use the following format for your formula:
SheetName!CellAddress
If the worksheet name in the reference includes one or more spaces,you must enclose it in single quotation marks. The following is a formula that refers to a cell on a sheet by the name of All Sales:
=A1*'All Sales'!A1
An easy way to enter a range reference on another worksheet is by pointing with the mouse.
If you need to reference a 3-D range in a function, enter the range in the following format:
=FunctionName(FirstSheet:LastSheet!RangeReference)
For example:
=SUM(Sheet2:Sheet4!$A$1:$A$10)
The following rules apply to 3-D range references:
- The range includes all sheets between the first and last sheet names in the reference, inclusive.
- If you insert another sheet between the first and last sheets, it will be included in the reference automatically.
- You can use absolute or relative references in the range reference.
- You can use any valid names for the sheets, but if any name has a space, you must use single quotes to enclose the names
in the reference. For example:
=SUM('Sales Engineering:Sales HR'!$A$1:$A$10)
Less than 10 percent of Excel's functions support 3-D range references and those that do fall mostly in the statistical category.
You can point with the mouse to create a 3-D reference. First position the mouse in the function where the reference will be placed. Then select the cell or range in the first worksheet that will be included in the reference. Next, press and hold Shift and click the sheet tab of the last sheet in the reference. Press Enter to complete the procedure.
