Formula Wizard

The Formula Wizard is a custom function powered by NecAccess that retrieves data from your ERP or other external systems and displays it directly in your Excel sheet. You can enter formulas manually or use the Formula Wizard from the Add-ins tab to generate them automatically.

Formulas created with the wizard behave like standard Excel formulas—when you copy, paste, or drag them, cell references adjust accordingly. However, formulas that use the List operation work differently: instead of being stored as formulas, they are saved as cell comments. This prevents accidental edits and avoids unnecessary 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: reate custom reports that always reflect the latest data.
• Performing calculations: Generate sums, counts, averages, and other aggregations without exporting data.
• Referencing dynamic inputs: Use parameters or cell references to drive calculated results.
• Displaying contextual information: Show related values such as descriptions, balances, or transactions.
• Eliminating manual refreshes: Automate reporting and avoid repetitive copy‑and‑paste workflows.

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 you want to retrieve.
4. Select the desired operation.
5. (Optional) To filter by a Reporting Tree node, select the reference cell defined in the Reporting Tree in the Reporting Tree node field.

note

If the formula uses a Reporting Tree node, it is automatically applied as a filter.

Available Operations

Operation	Description
Count	Count the number of values for the selected field.
Count Distinct	Count only unique values.
Description	Return the text label or description associated with the selected field.
Min / Max	Retrieve the smallest or largest value.
SUM	Calculate the total value.
SUM (Reversal)	Calculate a reversed total (commonly used for income statements).
SUM (Debit)	Sum debit values only.
SUM (Credit)	Sum credit values only.
List	Generate a list of values with customizable layout and behavior. When List is selected, a setup dialog appears where you can configure: List Insertion Method: Single comma delimited cell – Place all values in one cell. Replace existing cells – Overwrite adjacent cells with the list. Shift existing cells – Shift existing content to the right or down. List Orientation: Horizontal – Place values in adjacent columns. Vertical – Place 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: Ensure only unique values appear.
Dynamic List	Generate a comma‑separated list that updates automatically and is sorted in ascending order.

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. Define filter values using a prompt, a cell reference, or manual entry.
4. Select Ok to apply the filter. The generated formula appears in the Excel formula bar.

Use of wildcards for filtering

• ? replaces one character
• * replaces multiple characters

Wildcard example	Description
4000	Match exactly 4000
4*	Match any value starting with 4
4???	Match values starting with 4 and containing exactly four characters
4???-???-10	Match values starting with 4, followed by three characters, a dash, three characters, another dash, and ending with 10

Example - Sum by Account and Year

Use this scenario to return the total Ledger Posting amount for a chosen Account Code and reporting year (e.g., 2018). This helps you build dynamic reports where totals automatically update based on cell‑driven filters.

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