Reference to cells and cell ranges

Product:

Application: Table Desktop version

Referencing is linking to cells or ranges for further use in calculations. There are three types of references:

•Relative: This type of references changes when copying or auto-filling. By default, all references in the editor are relative.

•Absolute: This type of references does not change when copying or auto-filling. Therefore, they should be used for formulas that have constant values, such as interest rates.

•Mixed: This type of references allows you to combine both absolute and relative references. For example, in the В$5, D$12 reference the row number remains the same, but the column name may change.

А1 cell reference style

The A1 reference style is used in MyOffice Spreadsheet by default.

In the A1 reference style, the cell address consists of a column letter name and a numeric row name. For example: A1, C12, G37, ND185 etc.

When creating a sheet, the workspace contains:

•20 rows numbered from 1 to 20.

•10 columns named from A to J.

When you expand the workspace, new columns continue single-letter naming followed by two-letter naming: AA, AB, AC, then BA, BB, BC, etc. If the two-letter combinations end, the columns will be given three-letter names.

When you add rows to a table, the new items continue to be numbered.

Relative reference

А1

Absolute references

$A$1 (the column and row are not changed during copying)

Mixed references

$A1 (the column is not changed during copying)

A$1 (the row is not changed during copying)

R1C1 reference style

To enable the R1C1 reference style, in the View command menu, select R1C1.

In the R1C1 style, both columns and rows are denoted by numbers. The address of a cell is determined using the formula RnCm, where:

•R stands for Row, and n stands for row number.

•C stands for Column, and m stands for column number.

For example, the name of D5 cell in the R1C1 style is R5C4.

Absolute reference

R3C2 (an absolute reference to a cell at the intersection of row 3 and column 2)

R3 (an absolute reference to row 3)

C2 (an absolute reference to column 2)

Relative reference

RC (a relative reference to the current cell)

R[-2] (a relative reference to the row located 2 rows above the current row)

C[3] (a relative reference to the column located 3 columns to the right of the current column)

Mixed reference

RC5 (a reference to a cell in the current row in column 5)

RC[-5] (a reference to a cell located in the current row 5 columns to the left of the current cell)

R3C[2] (a reference to a cell located in row 3, 2 columns to the right of the current cell)

R[3]C[-2] (a reference to a cell located 3 rows below and 2 columns to the left of the current cell)

R[-3]C[2] (a reference to a cell located 3 rows above and 2 columns to the right of the current cell)

If a spreadsheet contains absolute references when switching from A1 to R1C1 reference style, they will remain absolute afterwards.

Cell range reference

The reference to a cell range is defined by referencing its two corner cells:

•Starting cell: The upper-left cell of the range.

•End cell: The lower-right cell of the range.

The starting and end cells are separated by the (:) operator. For example: B4:D6.

If the R1C1 style is selected for cells referencing, in some cases you don't need to use the (:) range operator. For instance, if you want to reference the range of all cells of one row, you can only specify a reference to this row (that is, the R1 reference refers to the range of all cells of the first row).

Was this helpful?

Yes