Formulas and cell references

FormCalc SST formulas are similar to those in other spreadsheet programs.

Formula Requirements

The main requirement, is that all formulas begin with an equal sign ('='):

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.

In the Snapshot sheet, cells containing valid formulas are highlighted with a yellow background, and those which have errors are highlighted with a red background.

Where You May Enter Formulas

This screenshot below shows the areas where you may enter formulas in FormCalc SST.

Most of your formulas will be entered in either these two areas:

•Formulas row:  formulas entered here will be applied to every row of data on your QuickBooks form except rows processed by Item-triggered formulas.

•Item-triggered formulas rows:  formulas entered on these rows get applied only to QuickBooks form rows which have a matching Item name. (This lets you control where special calculations like totals or subtotals get done, by your placement of certain Item names on the form.)

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 H14.

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:

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, FormCalc SST only allows formulas to refer to specific cell ranges on the Snapshot page. 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 error color (red background), or

2.A message will be displayed telling you FormCalc SST has adjusted (corrected) the formula's cell references.        

Allowed cell references:

Referring to "a whole column"—the entire range of Formulas (Detail) cells
...or "Close Enough" cell references

A frequent need in FormCalc SST formulas is to refer to an entire column of data in the Detail area of a QuickBooks form—to total the column, get an average or count of items in it, or to do other calculations based on the whole column. But since the entire possible range of Detail cells is not present in a snapshot, how do you tell FormCalc SST that is what you want?

The solution is simple:  have your formula refer to a cell range which includes the first and last row in the sample rows part of the Detail area. (Sample rows are the ones with tan backgrounds, immediately below the Formulas row.) For example, suppose you are creating a formula on the Item-triggered formulas 1 row, and you want it to total the Quantity column, which happens to be column H of the form snapshot. You would enter the formula something like this:

As the red box indicates in the screenshot below, this formula refers to a cell range which includes the first and last sample row in column H:

During form processing, FormCalc SST will convert this sample rows reference into a reference to the form's entire column of Quantity data.

Actually, there's an even easier way. Instead of the formula shown above, you could enter any of the following combinations (and many others):

In other words, just have your formula refer to the desired column (H in this case) and any range of rows above the Detail area of the snapshot. FormCalc SST will immediately and automatically convert what you enter to a reference to the appropriate range of sample rows. So the result of any of the three entries above will be:

Entering "whole column" cell ranges in the form "=SUM(H1:H1)" is easiest. That way, you don't have to be concerned with knowing the exact beginning/ending coordinates of the sample rows area.