Skip to main content

Create and Maintain Query Formulas

The Formula Builder is a tool used to aid in quickly creating and maintaining formulas in a Spreadsheet Server spreadsheet. Formulas available are based on the user's licensed features.

Note: See Create and Maintain GL Formulas for more information about GL-related formulas.

  1. Click the cell where the formula is to be inserted, or the cell containing the formula to be updated. In Excel, from the Spreadsheet Server Ribbon, select Formula Builder. The Formula Builder opens to the GXL or GEXQ panel, or, if Formula Builder was launched from a Spreadsheet Server formula, the corresponding formula panel appears.
  2. If applicable, select the desired formula in the Formulas tab. The selected formula panel appears.

    3. Note: For efficient processing, use cell references to identify individual query parameters. Where applicable, literal values are displayed, and can be typed.

    Cell References and Literal Values for Existing Formulas

    No Cell References

    • If there are no cell references in the formula, the cell reference fields appear blank. The formula uses only the hardcoded (literal) values.
    • If updating literal values but not cell references, only the literal values in the formula are updated.

    With Cell References

    • If there are cell references in the formula, the formula shows both the value and the reference to the cell containing said value, in the cell reference boxes.
    • If updating the cell references, the cell containing the value changes to the new reference, and the value is inserted in said cell only, if the cell is blank.
    • If updating the value, and there is a cell reference, the cell reference remains the same, and the value contained in said cell is updated.
    • If selecting a cell with a pre-existing value, the value in the formula is updated.
    Note:
    • Select cell references by selecting the desired field in the Formula Builder panel, or the desired cell in the worksheet.
    • If necessary, press F4 to lock either the row and/or column value of the cell reference.
  3. Use the following table to enter or modify data in the GEXQ panel:
    4. Field Description

    GEXQ:

    Returns a single value from the GEXQ (summary) portion of the query to the spreadsheet (see GEXQ Formulas for more information).

    Query Name

    Displays the selected query.

    Worksheet

    Displays the worksheet destination of the formula.

    Starting Cell

    Displays the cell where the formula resides.

    Field

    If the GEXQ summary field is to be defined within the formula, click the drop-down list, and select the field to be summarized.

    Note: Only fields assigned an alias appear in the drop-down list.

    Function

    If the GEXQ summary field is to be defined within the formula, click the drop-down list, and select the summary type (SUM, COUNT, MIN, MAX, etc.).

    Use/Parameter

    If applicable, specify or modify the cells containing the query parameters. A maximum of 25 parameters may be selected.

    Note: Values to be entered in the GEXQ formula vary, based on whether the GEXQ summary field is defined in the query or the formula.

  4. Use the following table to enter data or modify data in the GEXD panel:
    5. Field Description

    GEXD:

    Inserts query detail results to the spreadsheet (see GEXD Formulas for more information).

    Query Name

    Displays the selected query.

    Worksheet

    Displays the worksheet destination of the formula.

    Starting Cell

    Displays the starting cell of the template.

    Target Worksheet

    Type the name of the worksheet, or click the drop-down list, and select the worksheet destination for the output. Defaults to the current active worksheet.

    Target Type

    Specify whether the expanded detail should be inserted into a table, range, or row in the target worksheet.

    Target Cell/Range/Table

    Type the cell, range, or table, or, click the drop-down list, and select the desired range or table of the output target. If Column Headings is selected, the cell represents the left-most column heading as defined in the query; otherwise it represents the left-most cell of the first row of data. If the range or table does not exist, a prompt appears, allowing for the creation of the table.

    Fill Down Columns

    Specify the number of columns that contain formulas or values to the right of the results grid. These columns fill down to match the number of records in the results. Valid values are: 1, 2, 3, etc.

    Note:
    • If targeting a range, the fill down formulas or values must reside outside of the output range.
    • If Column Headings is selected, the fill down columns are required to be on the first row following the heading. If not selected, the fill down columns are required to be on the first two rows of the GEXD data.
    • In some instances, the GEXD Row function may be used in place of this option.

    Column Headings

    If selected, the generated output data will contain column headings from the query. Do not select this option if column headings will be manually entered in Excel.

    Auto Fit Column Sizes

    If selected, the system performs the Auto Fit Column Width feature of Excel, when the formula is executed.

    Clear Sheet/Range/Table

    If selected, the entire output sheet (specified in the Target Worksheet field), or the range or table (specified in the Target Cell/Range/Table field) is cleared when the GEXD formula is executed. Do not select this option if the GEXD formula or any of the query parameters are located in the output sheet.

    Note: See Clear a Range of Cells for clearing a range of cell data.

    Clear a Range of Cells

    If the Target Type is a cell, specify the range of cells to be cleared prior to executing the formula.

    Note: If using Fill Down Columns, exclude the first row from the clear range to avoid deleting the fill down formula or value (for example, if the Starting Cell is A7, the Clear Range of Cells must be A8:xx.

    Use/Parameter

    Select or modify the query parameters included in the formula.

  5. Use the following table to enter or modify data in the GEXS panel:
    6. Field Description

    GEXS:

    Opens a selector panel, based on query results (see GEXS Formulas for more information).

    Query Name

    Displays the selected query.

    Worksheet

    Displays the worksheet destination for the formula.

    Starting Cell

    Displays the starting cell of the template.

    Selector Text

    Specify or modify the label for the GEXS.

    Cell for Value

    Specify or modify the cell destination for the selected query value.

    Use/Parameter

    If applicable, specify or modify the cells containing the query parameters. A maximum of 25 parameters may be selected.

  6. Use the following table to enter or modify data in the GEXI panel:
    7. Field Description

    GEXI:

    Displays query detail in a drill down panel (see GEXI Formulas for more information).

    Query Name

    Displays the selected query.

    Worksheet

    Displays the worksheet destination of the formula.

    Starting Cell

    Displays the starting cell of the template.

    Query Parameters

    If applicable, specify or modify the cells containing the query parameters. A maximum of 25 parameters may be selected.

  7. The Formula results box near the bottom-left of the panel displays the formula as it is being built. Click the down arrow to expand and view the formula. Verify the data in the Results Box is correct, then click one of the following buttons:
    • Update: inserts the formula to the previously-selected cell in the current worksheet, and closes the panel.
    • Cancel: closes the panel.

    To copy the formula to the Windows clipboard, right-click the Formula box, and select Copy.

Was this article helpful?

We're sorry to hear that.

Powered by Zendesk