Formula Wizard

A Formula Wizard is a custom function powered by NecAccess that retrieves data from your ERP or external systems and displays it directly in your Excel worksheet.

You can write formulas manually or use the Formula Wizard from the Add-ins tab to generate them.

These formulas behave like standard Excel formulas—when you copy, paste, or drag them, references adjust automatically. However, formulas using the List operation work differently: instead of being saved as formulas, they're stored as cell comments to prevent accidental changes and avoid triggering recalculations in Excel.

When to use the Formula Wizard

• Retrieving real-time values: Insert ERP or external data directly into Excel cells.
• Building live dashboards and reports: Create custom reports using always-up-to-date values.
• Performing calculations: Generate sums, counts, averages, and more without exporting data.
• Referencing dynamic inputs: Use parameters or cell references to drive calculated results.
• Displaying contextual information: Show related values like descriptions, balances, or transactions.
• Eliminating manual refreshes: Automate reporting without repeated copy-pasting from source systems.

Create a formula using the formula wizard

1. In the Add‑ins tab, select Formula Wizard.
2. In the dialog, configure the Data Model / Environment Reference to query.
3. Choose the Field to retrieve.
4. Select the Operation.
5. (Optional) To filter by a Reporting Tree node, select the reference cell defined in Reporting Tree in the Reporting Tree node field.
   Note: When you edit this field, the formulas are automatically refreshed.

Apply dimension filters

1. Select a dimension to filter results for the specified field and operation.
2. Choose one of the following filter types:
   • List – Include or exclude specific values. Wildcards are supported.
   • From–To – Define a numeric or string-based range.
   • Select All – Include all values in the dimension (default).
3. Use a prompt or cell reference to define filter values, either by typing them manually or linking to another cell.
4. When ready, select OK. The generated formula appears in the Excel formula bar.

Use of Wildcards for filtering

• ? replaces one character
• * replaces multiple characters

Wildcard example	Description
4000	Matches exactly on a single value.
4*	Matches any value starting with 4.
4???	Matches any four-character value starting with 4.
4???-???-10	Matches a value starting with 4, followed by two groups of three characters separated by dashes, and ending in 10 (11 characters total).

Available Operations

Operation	Description
Count	Returns the number of values for the selected field.
Count Distinct	Returns the count of unique values.
Description	Returns a text label or description for the selected field.
Min / Max	Retrieves the smallest or largest value.
SUM	Returns the total value.
SUM (Reversal)	Returns a reversed total (e.g., for income statements).
SUM (Debit)	Returns the sum of debit values only.
SUM (Credit)	Returns the sum of credit values only.
List	Generates a list of values with custom layout and options. When List is selected, a setup dialog appears where you can set: List Insertion Method: Single comma delimited cell – All values appear in one cell. Replace existing cells – Values overwrite adjacent cells. Shift existing cells – Values shift existing content to the right or down. List Orientation: Horizontal – Places values in adjacent columns. Vertical – Places values in adjacent rows. List Ordering: None – No sorting. Ascending – Sort values A–Z or 0–9. Descending – Sort values Z–A or 9–0. Exclude Duplicates: Ensures only unique values appear.
Dynamic List	Generates a comma-separated, auto-updating list sorted in ascending order.

Example

Return the Sum of Ledger Posting filtered by Account Code for 2018

1. Open the Formula Wizard.
2. Select the data model to GL Transactions Details.
3. In the Field dropdown menu, select Ledger posting.
4. In the Operation dropdown menu, select Sum.
5. Under Dimensions Settings, select Account Code, choose List, and enter $B6.
6. Click Add selection to filter icon.
7. Under Dimensions Settings, select Year, choose List, and enter $B5.
8. Click Add selection to filter icon.
9. Click OK to insert the formula into the selected cell.