AX2348

Using cell references in Axiom functions

Instead of placing values directly into Axiom functions, you can place the values elsewhere in the spreadsheet and then use cell references. Use of cell references allows you to create more dynamic functions—by changing one or two values in a sheet, you can change many or all of the functions throughout the sheet.

For example, consider the following formula:

Instead of stating the column and filter within the function, you could place those values elsewhere and use a formula like the following:

When using cell references, the cell references are not placed in quotation marks, and the text in the referenced cells is not placed in quotation marks. The exception is the filter criteria—if the item referenced is text, it must be placed in single quotation marks within the referenced cell (such as ACCT.AcctGroup='Benefits').

Absolute cell references versus relative cell references

If the information being referenced is always in the same cell, then you should use dollar signs to "lock" the cell reference. When a reference is locked, then the formula will always point to the same cell, regardless of where the formula is copied into the sheet.

For example, if you use $E$5 as the cell reference, then the formula will always point to E5, even if you copy the formula to the next row down or the next column over. This is an absolute cell reference, where the reference always refers to the same cell.

But if you use just E5 as the cell reference, then when you copy the formula to the next row down, it will update to E6. And if you copy the formula to the next column over, it will update to F5. This is a relative cell reference, where the reference adjusts based on the relative position of the cell that contains the formula and the cell the reference refers to.

You can also use mixed cell references, where just the column or just the row is locked. If you lock just the column ($E5), then the column will remain at column E but the row will adjust. And if you lock just the row (E$5), then the row will remain the same but the column will adjust.

When using cell references in calc methods that will be copied throughout the sheet, make sure to use the appropriate type of cell reference so that the reference will adjust as needed or remain fixed as needed.

Using concatenation and embedded text

If part of the text used in a function is always the same, you can embed it within the function instead of repeating it in each referenced cell. For example, if the first part of each filter criteria statement is the same (ACCT.AcctGroup=), you can embed that text within the function, and place only the end of the criteria statement in the referenced cell (for example, Benefits, Salaries, etc.).

In this example, the function would look like the following:

The single quotation marks are necessary so that the end result is ACCT.AcctGroup='Benefits' (where Benefits is in cell C10). The double quotation marks around the &$C10& are necessary for the function to resolve the value as a string, and the use of ampersands is standard Excel functionality to concatenate the string.

Function parameters that take cell addresses

Some function parameters take cell addresses as their designated value. For example, when using GetDataElement, you can specify a cell address in which to place the selected value.

When specifying a cell address as a parameter value, you must place the cell address in quotation marks like any other text value. For example:

This example places the user's selected account in cell B20. If B20 is not in quotation marks, then Axiom Software will use B20 as a cell reference instead of as the designated parameter value. This will still work if cell B20 contains a cell address (or is blank).