Formula Wizard

The Formula Wizard builds NecAccess formulas that pull data from your ERP or other external systems and display the results directly in Excel. You can build formulas manually or let the wizard configure them for you from the Add-ins tab. Either way, the formula behaves like any standard Excel formula: copy, paste, or drag it, and cell references adjust automatically.

The one exception is the List operation. Formulas using List are saved as cell comments instead of regular formulas. It prevents accidental edits and stops Excel from recalculating the list every time the Excel file changes.

When to use the formula wizard

• Retrieving real-time values from your ERP or external system directly into Excel cells.
• Building live dashboards and reports that always reflect the latest data without manual exports.
• Performing aggregations such as sums, counts, and averages directly on source data.
• Referencing dynamic inputs by using parameters or cell references to drive calculated results.
• Displaying contextual information such as descriptions, balances, or related transactions alongside your data.
• Eliminating manual refreshes by automating repetitive copy-and-paste reporting workflows.

Create a formula

1. In the Add‑ins tab, select Formula Wizard.
2. Configure the Data Model / Environment Reference you want to query.
3. Choose the Field you want to retrieve.
4. Select the desired operation.
5. (Optional) Select the Reporting Tree node field and choose the cell reference defined in the Reporting Tree.

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 you select List, a setup dialog appears. Configure the following options bedore inseting the formula: 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 – push 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 applied. Ascending – sort values A to Z or 0 to 9. Descending – sort values Z to A or 9 to 0. Exclude Duplicates: show only unique values in the list
Dynamic List	Generate a comma‑separated list that updates automatically and is sorted in ascending order.

Sage Intacct Real Time

The List and Dynamic List operations are not supported for the Finance (Real Time) and Budget (Real Time) data models. Using either operation with these models returns an error. See Sage Intacct Real Time Formula Errors for more details.

Apply dimension filters

Dimension filters let you narrow down what data your formula retrieves. Instead of pulling everything from a field, you can limit results to a specific account, year, department, or any other dimension available in your data model. This keeps your formulas precise and your reports accurate.

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

• ? matches exactly one character
• * matches one or more characters

Example	What it matches
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

Scenario

Returns the total Ledger Posting amount for a specific Account Code and reporting year. Useful for building dynamic reports where totals update automatically based on values entered in other cells.

Details

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 Add selection to filter.
7. Under Dimensions Settings, select Year, choose List, and enter $B5.
8. Click Add selection to filter.
9. Click Ok to insert the formula into the selected cell.

Keep in mind

The filter values $B6 and $B5 are cell references, not fixed values. This means the formula automatically recalculates whenever the values in those cells change, making your report fully dynamic.