Formula Assistant

The Formula Assistant is a tool used to aid in quickly creating formulas within a Spreadsheet Server spreadsheet.

The Formula Assistant is applicable for GXA, GXD, GXE, GXU, and SSLDESC formulas.

1. In Excel from the SServer menu, select Formula Assistant. The Formula Assistant panel appears open to the GXA tab -or- if Formula Assistant was launched while on a SS formula, the panel opens the corresponding formula tab.

Nav Tip:This panel may also be accessed by selecting the SS toolbar or ribbon equivalent (see SS Toolbar -or- SS Ribbon).

2. Select the tab of the desired formula type. The selected formula panel appears.

Note: For efficient processing use cell references to identify individual formula parameters. However, if literal values are keyed in the entry boxes, they must be placed in doubt quotes (").

Hint: Shortcut for selecting cell references:

· On the worksheet, select the desired cell.

· On the Formula Assistant panel, double click in the desired text box to paste the cell reference of the cell previously selected on the worksheet.

Hint: If necessary, use F4 to lock either the row and/or column value of the cell reference.

3. Use the table to enter formula specific data on the GXA - Formula Assistant panel.

Field Description

GXA: Returns a single balance from the ledger. For more information refer to GXA Formula for Account Values.

Column Parameters: This area assumes that the parameters will be laid out in a column, so entering the first value and clicking on the down arrow will populate the rest of the fields. For example, if the cell reference entered for the ledger type field is H1, then clicking on the down arrow will populate the following fields with H2, H3, etc.

Year Identify the reporting year.

Format Identify the time range for which to retrieve balances. Value formats are PER, QTR, YTD, and LTD.

Period Identify the period, quarter number or range of periods (i.e. 1.5 for periods 1 thru 5) for the specified format.

Currency Code Identify a valid currency code, or leave blank when retrieving statistical balances.

Balance Type Identify the balance type to be retrieved. Value balance types are M (monetary), S (statistical), D (debit balances), C (credit balances), 1 (numeric user field 1) and 2 (numeric user field 2).

Note: Currency code must be blank when balance type is S.

Budget / Actuals Identify whether to retrieve actual or budget data. Valid values are ACTUALS or the name of the budget.

Database Lib If necessary, specify the database library from which to retrieve the data.

The library entered here will override the library keyed during the sign on process and/or the library defined in the Configurator.

Account String:

Company, Seg2, Seg3, etc.

Identify the required accounts segments to be used by the formula. This area assumes that the segments will be laid out next to each other on one row, so entering a value and clicking on the right arrow will populate the rest of the fields. For example, if the cell reference entered for the first account segment is A13, then clicking on the right arrow will populate the following fields with B13, C13, etc.

Note: The number of segments available is based upon the Max Segments Used defined the Settings.

Append AND / OR User List Select the check box to indicate to add an AND/OR user list comparison to the account string criteria.

AND / OR Identify the appropriate user list criteria operand. Valid values are AND (account must meet both the account string and user list criteria) and OR (account must meet either the account string or user list criteria).

User List Specify the user list to be used by the formula.

Target Cell Displays the formula's target cell location.

Building Spreadsheets 28

Global Software, Inc.

4. Use the table to enter formula specific data on the GXD - Formula Assistant panel.

Field Description

GXD: Returns the account description for a particular account. For more information refer to GXD Formula.

Account String:

Company, Seg2, Seg3, etc.

Identify the required accounts segments to be used for retrieving the account description. This area assumes that the segments will be laid out next to each other on one row, so entering a value and clicking on the right arrow will populate the rest of the fields. For example, if the cell reference entered for the first account segment is A13, then clicking on the right arrow will populate the following fields with B13, C13, etc.

Note: The number of segments available is based upon the Max Segments Used defined the Settings.

Database Lib If necessary, specify the database library from which to retrieve the data.

The library entered here will override the library keyed during the sign on process and/or the library defined in the Configurator.

Target Cell Displays the formula's target cell location.

5. Use the table to enter formula specific data on the GXE - Formula Assistant panel.

Field Description

GXE: Explodes summary line data into detail data lines. For more information refer to Expand Detail Reports - Account Details, and

Expand Detail Reports - Journal Entry Details.

Account Details: This area assumes that the parameters will be laid out in a column, so entering the first value and clicking on the down arrow will populate the rest of the fields. For example, if the cell reference entered for the target sheet name is B13, then clicking on the down arrow will populate the following fields with B14, B15, etc.

Target Sheet Name Identify the name of the worksheet that contains the final formatted worksheet and will receive the expanded account details.

Source Sheet Name Identify the name of the worksheet that contains the summary formulas that will be expanded.

Target Row # / Range Name Identify the first row number or the range name on the target worksheet to begin inserting the expanded detail.

Source Row # Identify the row number in the source worksheet that contains the summary formulas.

Building Spreadsheets 30

Global Software, Inc.

Field Description

Journal Entry Details: This area assumes that the parameters will be laid out in a column, so entering the first value and clicking on the down arrow will populate the rest of the fields. For example, if the cell reference entered for the GXA source column is E13, then clicking on the down arrow will populate the following fields with E14, E15, etc.

Generate Journal Detail Sheet

Select the check box to indicate to include an expansion of journal entry details as part of the GXE formula.

GXA Source Column to User Identify the column on the source worksheet for which to expand journal entry details.

Target Sheet Name Identify the name of the worksheet that will receive the expanded journal entry details.

Target Starting Cell Identify the starting cell location on the target worksheet to begin inserting the exploded journal entry detail.

Include Headings Indicate whether or not to include headings when expanding journal entry details.

Autofit Columns Indicate whether or not to autofit columns when expanding journal entry details.

Generate Totals Indicate whether or not to generate account subtotals as well as a grand total when expanding journal entry details.

Convert to Excel 2007 Table Indicate whether or not to place the expanded journal entry details into a table. When this option is utilized, the system creates a new table on the specified target sheet and assigns the next available standard Excel table name. In addition, headings are always included and a grand total line is generated; however, no account subtotals are generated.

Target Cell Displays the formula's target cell location.

6. Use the table to enter formula specific data on the GXU - Formula Assistant panel.

Field Description

GXU: Returns an account balance using user field references. For more information refer to GXU Formula for User Fields.

Column Parameters: This area assumes that the parameters will be laid out in a column, so entering the first value and clicking on the down arrow will populate the rest of the fields. For example, if the cell reference entered for the ledger type field is H1, then clicking on the down arrow will populate the following fields with H2, H3, etc.

Year Identify the reporting year.

Format Identify the time range for which to retrieve balances. Value formats are PER, QTR, YTD, and LTD.

Period Identify the period, quarter number or range of periods (i.e. 1.5 for periods 1 thru 5) for the specified format.

Currency Code Identify a valid currency code, or leave blank when retrieving statistical balances.

Balance Type Identify the balance type to be retrieved. Value balance types are M (monetary), S (statistical), D (debit balances), C (credit balances), 1 (numeric user field 1) and 2 (numeric user field 2).

Budget / Actuals Identify whether to retrieve actual or budget data. Valid values are ACTUALS or the name of the budget.

Database Lib If necessary, specify the database library from which to retrieve the data.

The library entered here will override the library keyed during the sign on process and/or the library defined in the Configurator.

Building Spreadsheets 32

Global Software, Inc.

Field Description

Account String:

Company, User Field 1, User Field 2, User Field 3, User Field 4

Identify the required company and user fields to be used by the formula.

This area assumes that the segments will be laid out next to each other on one row, so entering a value and clicking on the right arrow will populate the rest of the fields. For example, if the cell reference entered for the first account segment is A13, then clicking on the right arrow will populate the following fields with B13, C13, etc.

Target Cell Displays the formula's target cell location.

7. Use the table to enter formula specific data on the SSLDESC - Formula Assistant panel.

Field Description

SSLDESC: Returns the description for a particular segment list. For more information refer to SSLDESC Formula.

Value Cell Identify the cell location containing the segment list value for which to retrieve the segment list description.

Target Cell Displays the formula's target cell location.

8. After the appropriate data has been entered and verified, click one of the following buttons.

· Excel -- copies the formula to the previously selected cell on the current worksheet.

· Clipboard -- copies the formula to the Windows clipboard.

· Cancel -- closes the panel.

9. To enter or modify formula parameters for another cell, while the Formula Assistant panel is still open select the desired cell on the Excel worksheet and click the Reset button. The Formula Assistant displays the appropriate formula tab panel and displays the parameters for the formula for the selected cell.