Dynamic Ranges is a powerful feature that keeps your reports up to date by automatically pulling in live data from Datarails for chosen fields. Your reports dynamically restructure as data changes - when new accounts, departments, or line items are added, the range expands automatically, and formulas are applied across all rows.

Overview

A Dynamic Range consists of two parts:

• Line Items Range - The column(s) where unique dimension values are written (e.g., account names, cost centers). This list updates automatically when new data is added to the connected table.
• Formula Range (optional) - The columns containing DR.GET formulas or other Excel formulas. When the line items range expands or contracts, formulas from the first row are automatically applied to all new rows.

The result is a self-maintaining report: the line items range always reflects the current data in Datarails, and the formula range keeps pace.

Prerequisites

Before creating a Dynamic Range:

1. Your Excel file must be connected to a Datarails Filebox.
2. The file must contain at least one table with the fields you want to use as dynamic dimensions.
3. Set up your report template with the layout you want - column headers, dimension labels, and at least one DR.GET formula in the first data row of the formula range.
4. The line items range and formula range must be on the same worksheet.
5. Neither the line items range nor the formula range can overlap with an Excel Table (ListObject).

Creating a Dynamic Range

Step 1: Prepare the Template

1. In your Excel worksheet, set up the layout with column headers (e.g., Account, Department) and time periods.
2. In the first data row, write placeholder text where the line item values will go (this becomes your Line Items Range).
3. In the adjacent columns, write DR.GET formulas using relative cell references pointing to the line items cells. Copy the formula across the first row to ensure references shift correctly.

Tip: Use relative references for the cells that should change per row (e.g., account name), and absolute references for fixed values (e.g., $A$1 for a header cell).

Step 2: Add a New Dynamic Range

1. Open the Datarails task pane and navigate to the Dynamic Ranges section.
2. Click Add Dynamic Range.
3. Enter a descriptive name (e.g., "Income_Accounts_FY2025").
4. Click Save to open the Dynamic Range editor.

Step 3: Configure the Range

1. Formula Range: Select the cells containing your DR.GET formulas. If you have a Total column, include it in the selection.
2. Line Items Range: Select the cells in your worksheet where the line items will appear. Click the selection button to capture the range.

• You can select multiple columns if your line items include more than one field (e.g., Account Name + Account Code).

3. Line Items Field Selector: Click Select to open the field chooser.

• Choose the table from the dropdown.
• Select the fields you want as your line items fields and add them to the Display Fields area.
• The number of fields must match the number of columns in your Line Items Range.
• The order of fields determines column placement: first field = leftmost column.

4. Filters (optional): Add filters to narrow the list to specific values (e.g., only "Expenses" under Level 1). You can later use the selected filters to split your dynamic range to sections (explanation below).

Step 4: Complete Setup

1. Review the settings. Optionally enable:

• Zero Suppression - Hides rows where all formula cells return no data.
• Disable Line Items Update - Keeps the existing line items fixed on refresh; only formula values update.
• Preserve Static Values - Preserves cells without formulas (static values like typed-in numbers or text) from being overwritten on refresh (see details below).

2. Click Done.
3. Submit the file to save changes.

Managing Dynamic Ranges

Open the Dynamic Ranges section in the Datarails task pane to see all existing Dynamic Ranges in the workbook. From here you can:

• Select a range - Clicking a Dynamic Range highlights it in the worksheet and navigates to its location.
• Edit - Modify the formula range, line items range, line items fields and filters.
• Rename - Change the Dynamic Range name.
• Duplicate - Create a copy of an existing Dynamic Range. 
  The duplicate copies the full configuration - formula range, line items range, field selection, filters, and per-range settings - into a new Dynamic Range with a default name. You can then edit the duplicated range to adjust the target range location or modify any settings as needed. This is useful when you need multiple similar Dynamic Ranges with slight variations.
• Delete - Remove the Dynamic Range.
• Add new - Create additional Dynamic Ranges.

Per-Range Settings

Each Dynamic Range has independent settings that you can toggle on or off:

Zero Suppression

When enabled, after a refresh the system scans all DR.GET formula cells in the range. If every formula cell in a row returns a missing or error value, that entire row is removed. This keeps your report clean by hiding line items with no data.

Disable Line Items Update

When enabled, the line items stay fixed on refresh. Only the formula values are recalculated. This is useful when you've manually reordered or customized the line items and don't want them overwritten.

Preserve Static Values

When enabled, cells in the formula range that do not contain any formula (i.e., cells with raw numbers, text, or other manually entered values) are preserved during refresh. Without this setting, the refresh would overwrite these cells by copying the first-row formula down to all rows.

This replaces the global "Forecast Mode" setting from the legacy COM add-in - you now have per-range control instead of a single file-level toggle.

Example: You have a Dynamic Range for expense accounts with DR.GET formulas pulling actuals. For forecast months, users type in budget numbers directly. With Preserve Static Values enabled, those typed-in numbers survive every refresh.

Sections

Sections is a new capability in the Office JS add-in that allows you to split a single Dynamic Range into multiple sections on the same sheet, instead of creating separate Dynamic Ranges for each group. This simplifies workbook management and keeps related data organized within one Dynamic Range.

How Sections Work with Filters

Sections are driven by the filters you select when configuring the Dynamic Range. The filter values you choose become the available options for splitting into sections.

Example: If you add a filter on the Department field, the Sections dropdown will show the department values (e.g., Sales, R&D, Marketing). You then assign each section a row range and a department value, so the Dynamic Range knows which rows belong to which department.

Setting Up Sections

1. When creating or editing a Dynamic Range, add a filter on the field you want to use for sectioning (e.g., Department, Entity, Cost Center).
2. Navigate to the Sections configuration.
3. In the dropdown, you'll see the values from your selected filter field.
4. For each section, define:

• The row range in the worksheet that belongs to this section.
• The filter value that this section represents.

5. Repeat for each value you want as a separate section.

Example setup:

• Rows 5–10 → Sales department
• Rows 11–15 → R&D department
• Rows 16–20 → Marketing department

Each section refreshes independently with only the data matching its assigned filter value, while all sections remain part of the same Dynamic Range.

Why Use Sections Instead of Multiple Dynamic Ranges

• Simpler management - One Dynamic Range to configure, rename, or delete instead of several.
• Shared settings - All sections inherit the same formula range template, field selection, and per-range settings (Zero Suppression, Preserve Static Values, etc.).
• Cleaner layout - Keeps related groups on the same sheet with clear visual separation between sections.

Filtering Dynamic Ranges

You can refine which data appears in your Dynamic Range by adding filters:

1. Open the Dynamic Range editor.
2. In the Filters section, add a filter condition (e.g., Entity = "CAD").
3. Save the filter. The range will refresh and display only matching items.

Filters are dynamic - you can change them at any time, and the range adjusts accordingly.

Tips & Best Practices

• Name your ranges descriptively - Include the field name/scenario so they're easy to identify in a workbook with multiple Dynamic Ranges.
• Use relative references carefully - In the formula range, ensure DR.GET references to the line items cells use relative row references so they shift correctly when formulas are copied down.
• All line items data is written as text - The system formats line item values as text (@ format) to prevent Excel from auto-converting values like "1-2" to dates or "001" to numbers.
• Submit after changes - Always submit your file after creating, editing, or deleting Dynamic Ranges to save the configuration to Datarails.

Updated August 27, 2026 14:37