Working with Data

A form sheet contains cells, which values can be specified explicitly, get on setting up an attribute or table area from source or calculate by formula.

Data Entry and Editing Cell Contents

To enter or edit data in a cell, select a cell on a report sheet and enter necessary data.

Copying and Pasting Data

Data copying, moving, and pasting from the selected cell or area is similar to working with data via clipboard for table area. To correct pasted data, use special paste.

Deleting Data

To delete cell or cell range contents, select the required cell or cell range and press the DELETE key.

Adding Comments

To add explanations, remainders, or comments for a sheet cell, use the comments function..

Adding Named Ranges

To quickly search for the required cell range and also when creating formulas, use named ranges..

Using Formulas

A formula starts with the = sign and can include constants, statements, and cell references.

A constant is a ready (non-calculated) value. Examples of constants: date 10.10.2015, number 210 and text "Quarterly profit". Expression or its value are not constants.

If a formula does not contain references to other cells (for example, a formula looks as =30+70+110), value in such cell is changed only after manual changing of the formula.

Statements are operations that should be executed with formula operands. There is a standard order of calculation execution but it can be changed by means of brackets.

Arithmetic and comparison statements can be used to create formulas.

Arithmetic statements are used to execute arithmetic operations: addition, subtraction, multiplication. Operations are executed with numbers. The following arithmetic statements are used:

Sign	Description
+	Addition.
-	Subtraction.
*	Multiplication.
/	Division.

Comparison statements are used to compare two values. The comparison result is a logical value: True or False.

Sign	Description
=	Equal.
<	Less.
>	Greater.
<=	Less or equal.
>=	Greater or equal.
<>	Greater or less (not equal).

Special features of using references to cells of report sheet in a formula:

Single cell. A sheet cell address is specified, for example, B8.
Column. To use all cells of the same column in a formula, specify column name via a colon, for example, B:B. To use all cells of several columns in a formula, specify their names via a colon, for example, A:C.
Row. To use all cells of the same row in a formula, specify row number via a colon, for example, 5:5. To use all cells of several rows in a formula, specify their numbers via a colon, for example, 1:5.
Cell range. To use a cell range in a formula, first specify address of the upper left cell, where the range starts, next, after a colon, specify address of the bottom right cell where the range ends, for example:

C7:D15

Where:

C7. The address of the upper left cell.
D15. The address of the lower right cell.

Combined cell range. To describe an area that contains several not intersecting ranges (including single cells), specify addresses of rectangular areas and individual cells, separating them with a semicolon (;). For example:

C7:D15;A1:B4;B8

Where:

C7:D15 and A1:B4. Cell ranges.
B8. Single cell.

Reference to cells of other sheet. To use a cell (or a cell range) located on another sheet, specify sheet name in quotes and with an exclamation mark before the range.

A link to a sheet is inserted following these steps:

The sheet name is inserted into cursor position when the user moves to another sheet. If a term was selected, sheet name is inserted into selected term position. No objects are selected on the active sheet.
Select the required range on the sheet - references to a range are inserted into cursor position. The range is selected with a dashed frame.
Changing the active sheet adjusts sheet name in the output term, the reference to range is not adjusted, it corresponds to selection on each of the activated sheets. Changing borders of the range selected on the active sheet results in adjusting the reference to this range in the output term.
When the active sheets changes, the cursor remains within the cell editor or in the formula bar, depending on its position before this event.
If the formula is entered with a cell editor, and another sheet is activated or a term is inserted on entering the formula, when the user goes back to the current sheet, the editor is restored, and the cursor is placed after the added term.
After finishing to enter formula, the sheet, to which the formula was entered, becomes active.

Formula editing stops when another sheet is activated if:

The cursor is positioned after any term.
The cursor is within a term.
Several characters that are not a term are selected: a cell address (A0) and a sheet name with the exclamation mark (“Sheet1”!).

When other sheets are activated during formula entry, the current sheet is highlighted. References to other sheets in formula or on a sheet are not highlighted in color. Examples of references:

="Sheet1"!A3

Value of the A3 cell from the sheet 1.

=Sum("Sheet2"!A:A)

Sum of values of the A column from the sheet 2.

=Sum("Sheet3"!C31;A31;E31)

Sum of combined range: value of the C31 cell is taken from the sheet 3, values of the A31 and E31 cells are taken from the current sheet.

=Sum("Sheet2"!B31:D31)

Sum of value range from the sheet 2.

The selected cell or range is highlighted using a dotted color frame. When entering statements or signs separating function parameters, a cell is fixed and highlighted using solid color frame. The next mouse click specifies the address of another cell. Use the mouse to move, increase or decrease a highlighted cell or range, the formula address automatically changes. To extend or reduce a range, point the cursor to cell corner and after it looks as a double-headed arrow drag the range border in the required direction.

Input or edited cell (range) address and the appropriate cell (range) borders have the same color.

Operations with Formulas

A formula can be created in the following ways:

Directly in the cell. To do this, execute the following operations in the cell to edit:

Enter the equality sign. Skip this step if the cell already contains this sign.
Create a formula using numbers, statements, links to cells and functions.
Press the ENTER key to save and calculate the formula.

In formula bar.
Using function wizard. To do this, click the Insert Function button in the formula bar or press SHIFT+F3.
By copying the cell with the created formula. Execute the following operations:

Copy the cell with formula.
Select the Paste Special item in the context menu of the cell, to which a formula should be added. Select the Cell Formulas checkbox.
Click the OK button.

To cancel entering formula, press the ESC key.