A formula can have references to cells on the same sheet or to cells on other sheets, as well as ranges of cells on sheets.
In the examples shown below, we use A1 (Letter-Number) notation for the cell reference, but the same would be valid for R1C1 (Number-Number) notation. Simply precede the cell reference, regardless of the style, with the sheet name as described here.
For more information on cell references that do not include sheet names, refer to Cell References in a Formula.
When a reference to a cell includes a reference to a cell on another sheet, this is called cross-sheet referencing.
An example of cross-sheet referencing in a formula that uses the addition operator would be:
(FirstRoundData!A2 + SecondRoundData!A2)
where the name of one sheet is "FirstRoundData" and the name of another sheet is "SecondRoundData". Sheet names precede the cell reference with the name of the sheet followed by an exclamation point (!). This formula could be on any sheet in the Spread since it explicitly names the sheets of each of the cells as operands. This example adds the values in the cell A2 on two different sheets. By making the sheet name explicit there is no confusion as to which cell A2 is meant. If you do not include the sheet name, the current sheet (in which the formula exists) is used. If the formula in the above example was on the SecondRoundData page, then the formula could be written as:
(FirstRoundData!A2 + A2)
It might be less confusing to put the cell on the current page first, as in:
(A2 + FirstRoundData!A2)
When a reference to a cell includes the same cell or a cell range on multiple sheets, this is called three-dimensional referencing.
An example of 3-D referencing in a SUM formula that uses the cell range on multiple sheets is described below.
SUM(Sheet1:Sheet10!A1:A2)
where the name of one sheet is "Sheet1" and the name of another sheet is "Sheet10". This formula uses SUM function to create a 3-D reference that executes the sum operation across multiple sheets via adding up the values in cell range A1:A2 in all the sheets that lie betweeen Sheet1 to Sheet10.
3-D Referencing can be extensively used to quickly calculate data across multiple spreadsheets that possess identical pattern and identical data type.
As long as the sheet name conforms to normal variable name rules (with the first character being a letter or an underscore and the remaining characters being letters, digits, or underscores) then the formula can use just the sheet name followed by the exclamation point. Otherwise, the sheet name needs to be enclosed in single quotes. If the sheet name itself contains a single quote, then use two single quotes in the formula. For example, if the name of the sheet includes a single quote (or apostrophe) as in these names for sales of a given month, then a reference to the sheet would look like this in a formula:
('November''s Sales'!A2 + 'December''s Sales'!A2)
with two single quotes (or apostrophes) before the s. If the sheet name has a space, use single quotes around the sheet name. In the following example the sheet name is East Coast Sales.
('East Coast Sales'!A2 + 'West Coast Sales'!A1)
If you have a quote in the name of the sheet, you need to add the delimiter that is required for that language. For instance, in C#, if the sheet name is "Zippy" Sales, where the quotes are part of the sheet name, a formula that includes a reference to this sheet might look like this:
('/"Zippy/" Sales'!A2 + 'West Coast Sales'!A1)
where a single quotes surrounds the entire sheet name and the backslash (/) delimiter precedes the quotes. For Visual Basic, you would use two double quote characters as in:
('""Zippy"" Sales'!A2 + 'West Coast Sales'!A1)
For cross-sheet referencing of a range of cells in another page, precede the range with the sheet name. For example:
SUM(SecondRoundData!A2:A10)
This adds the values in cells A2 to A10 of the sheet named SecondRoundData. There is no reason to include the sheet name in the second half of the range reference since the cells are on the same sheet. You cannot specify two different sheets in a range; a range of cells is only on a particular sheet, not between sheets.
Return to the Formula Overview.
