A formula can reference the contents of one or more other cells. The cells can be referenced within the current worksheet (internal references), or from another worksheet (external references) within the same file.
External references must include the full path name to the other worksheet. The following table shows the syntax for entering an external reference into a formula.
Syntax |
Example |
|
worksheet name:cell address |
=MyWorksheet:A1 |
|
worksheet name:range of addresses |
=SUM(MyWorksheet:A1..A12) |
If the name of the worksheet contains spaces, the name must be enclosed with single quotes as in the following example: ='Appliance Schedule':A1
To update an external reference, select File > Recalculate from the Worksheet menu.
Cell references in a worksheet can be either relative or absolute. When the formula that contains the reference is moved, an absolute reference always refers to the original cell address, while a relative reference changes depending on the location of the cell that contains the reference.
Use the dollar sign ($) character to indicate an absolute reference. The $ character locks the part of the cell reference it precedes, as described in the following table.
Combination |
Description |
|
$A1 |
Locks the specified column reference but leaves the row reference relative; the same column is always referred to, but the row changes if the formula is placed in a different row |
|
$A$1 |
Locks both the specified column and row references; regardless of where the formula is copied, it always refers to the original cell |
|
A$1 |
Locks the specified row reference but leaves the column reference relative; the same row is always referred to, but the column changes if the formula is placed in a different column |
In the following example worksheet, the formula =AVERAGE(B1..B3) is in cell B4. If the formula were copied to cell E9, the formula would automatically be changed to =AVERAGE(E6..E8). Because the references are relative, both the column and row would change relative to the cell where the formula is placed—always indicating the three cells directly above the formula.
~~~~~~~~~~~~~~~~~~~~~~~~~
