Spreadsheet cells have addresses that you can use to make
relative or absolute references to cells in the same spreadsheet,
or in other spreadsheets.
- Cell addresses
- Cells are addressed in terms of column and row. For example, the
first cell on a spreadsheet is addressed as A1, since it is marked
by column A, row 1. To specify the address for a range of cells, enter
the address of the upper left cell in the range, followed by a colon
(:), followed by the address of the lower right cell in the range.
For example, a square formed by the first 4 columns and first 4 rows
of a spreadsheet is specified as A1:D4.
- Cell addresses are used to create relative or absolute references
in a spreadsheet.
- Relative references
- Relative references are automatically adjusted when new columns
or rows are inserted into a spreadsheet. For example, assume that
you want to calculate in cell E1 the sum of the cells in the range
A1:C3. The formula to enter in cell E1 would be =SUM(A1:C3). If you
later decide to insert a new column in front of column A, the elements
that you want to add would then be in the range B1:D3 and the formula
would be in cell F1, not in cell E1. After having inserted a new column
A, the formula =SUM(A1:C3) will be automatically updated to =SUM(B1:D3).
Row numbers are also automatically adjusted, for example when a new
row 1 is inserted.
- Absolute references
- Absolute references are used when a calculation refers to one
specific cell in your spreadsheet. If a formula that refers to exactly
this cell is then copied relatively to a cell below the original cell,
the reference is also moved down if you did not define the cell coordinates
as absolute.
- A dollar sign ($) is placed before the column letter and row number
of a cell address for an absolute reference, for example $A$1. You
can convert a reference, in which the cursor is positioned in the
input line, from relative to absolute by pressing Shift +F4. For example,
if you start with a relative address such as A1, the first time you
press this key combination, both the column and row are set to absolute
references ($A$1). The second time, only the row (A$1), and the third
time, only the column ($A1). If you press the key combination once
more, both column and row references are switched back to relative
(A1)
Both absolute and relative references are adjusted
whenever the referenced area is moved. But be careful if you are copying
a formula because, in that case, only the relative references will
be adjusted, not the absolute references.
In addition to changing
when new rows and columns are inserted, references can also change
when an existing formula that refers to particular cells is copied
to another area of the spreadsheet. For example, assume that you enter
the formula =SUM(A1:A9) in row 10. If you want to calculate the sum
for the adjacent column to the right, copy this formula to the cell
to the right. The copy of the formula in column B is automatically
adjusted to =SUM(B1:B9).
- Referencing cells from other sheets in the same spreadsheet
- To reference a cell from another sheet in the same spreadsheet,
specify the sheet and cell. For example, If you have a spreadsheet
that has two sheets called Sheet1 and Sheet2, and you want any change
made to cell A1 on Sheet1 to also appear in cell A1 on Sheet2, go
to cell A1 on Sheet2 and enter =Sheet1.A1.
Now, any value entered into cell A1 on Sheet1 will also appear in
the same cell on Sheet2.
- Referencing cells in another spreadsheet
- To reference a cell in another spreadsheet, you need to reference
both the source document and the cell address. For example, assume
you have a spreadsheet called C:\test\test.ods saved
locally, and it contains a sheets called Sheet1. You now want to reference
cell A1 on Sheet1 of the saved spreadsheet in a new spreadsheet.
- Place the cursor in a cell on your new spreadsheet. Enter ='C:\test\test.ods'#Sheet1.A1 to
add the reference. As a result, the new sheet name is preceded with
a dollar sign ($) that designates it as an absolute reference.