IBM Lotus Symphony


Referencing cells in spreadsheets

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.

Product Feedback | Additional Documentation | Trademarks