FullSpeed formulas are similar to those in other spreadsheet programs.
Formula Requirements
The main requirement, is that all formulas begin with an equal sign ('='):
=SUM(E19,B7:B9)
=34.5*H14
=TODAY()
Beyond this requirement of course, the formula must also be "legal"—it must be constructed properly and cannot reference out-of-bounds cells or cell ranges, etc.
 | Cells with valid formulas are highlighted in yellow. Cells with formula errors are highlighted in orange. |
Where You May Enter Formulas
You can enter formulas in two areas of a macro worksheet:
| ® | The Formulas row is the main place you will enter formulas. The formulas you enter here will be applied to each row in the target application except rows which cause text-triggered formulas (see below) to be triggered. |
| ® | Text-triggered formula rows are for formulas that will be calculated only where specific text appears in the target application. You enter the text to be matched in the worksheet column you've marked as the Match column (described below). Then when the macro runs, the formula will be triggered on row(s) of the target application where matching text is found. |
Text-triggered formulas are most often used for column subtotals, totals, and the like. A worksheet can have up to five text-triggered formula rows—five sets of formulas, each triggered by the appearance of different text in the target application.
| Text-triggered rows are shown in alternating colors to help distinguish them from each other. |
| Text-triggered formulas are easier to use than to explain! See the Basic steps and Problem Solved sections for examples. |
|
Formulas vs. text-triggered formula rows... Formulas that you want applied to each line of data in the target application go on the Formulas row. Formulas that you want applied only to specific lines go on a text-triggered formula row. |
References to Other Cells and Cell Ranges
Cell references in formulas
To refer to another cell in a formula, enter the cell's coordinates. For instance, the following formula multiplies 34.5 times the value in cell H5.
=34.5*H5
To refer to a range of cells, specify the coordinates of the upper left and lower right cells of the range, separated by a colon ( : ). This formula sums the range of cells consisting of B7, B8, and B9:
=SUM(B7:B9)
| As you may have guessed from the "upper left and lower right" phrase in the above paragraph, a cell range may refer to a block of cells which spans more than one row and/or column. |
Cell referencing limitations
To prevent errors, FullSpeed only allows formulas to refer to specific cell ranges on a macro worksheet. Each formula is checked as you enter it, and if the formula refers to an out-of-bounds cell one of two things will happen:
| 1. | The formula will be highlighted in the warning color (orange background), or |
| 2. | A message will be displayed telling you FullSpeed has adjusted (corrected) the formula's cell references. |
Allowed cell references:
Formulas row formulas |
Can only refer to other cells on the Formulas row, and to cells on the Formulas anchor row. |
Text-triggered formulas |
Can refer to other cells on the same text-triggered formula row (i.e., not to cells on other text-triggered rows), and to cells on the text-triggered row's anchor rows. |
A frequent need in FullSpeed formulas is to refer to an entire column of data in the target application—to total the column, get an average or count of items in it, etc.. But how do you tell FullSpeed that is what you want your formula to do?
The solution is simple: have your formula's cell range refer to the first and last row in the Sandbox. For example, suppose you are creating a formula on a text-triggered formulas row and you want it to total the Quantity column, which happens to be column E. You could enter the formula like this:
=SUM(E8:E10)
As the red box indicates, this formula references the first and last rows of the Sandbox row in column E.
That's all there is to it! During form processing, FullSpeed will convert the Sandbox cell reference into a reference to the form's entire Quantity column.
Referring to a whole column of data in formulas is such a common need, that FullSpeed has been given a special feature to make it easier.
Counter to what's implied in the example above, you do not have to specify the exact first and last rows of the Sandbox to enter a "whole column" formula. Simply enter a cell range which refers to row 0 or row 1 in the desired column. The formula described in the example above could have entered either as:
=SUM(E0:E0)
or...
=SUM(E1:E1)
After you've entered a formula his way, FullSpeed will immediately adjust the coordinates to refer to the range of Sandbox cells in the same column; in this case:
=SUM(E8:E10)