Datarails Flex Add-In: Dynamic Ranges

Dynamic Ranges is a powerful feature that ensures your file stays up to date by automatically pulling in new data from the system for a chosen field. Your reports dynamically restructure according to live data.

For example, you have a list of Accounts under Income and for each Reporting Month, you are pulling the amounts with DR.GET, while referencing the Accounts cells. When you activate Dynamic Range:

  1. List of Accounts is generated, and the list keeps updating whenever new data is added to the table. 

  2. The formulas from the first row is applied across the range you set.

  3. The result is a dynamic list of unique values in the first column, with corresponding formulas populating other cells.

Use Cases

Dynamic Ranges are particularly beneficial for scenarios where new data frequently impacts reports. For example, Forecast use case. They ensure that:

Only formulas (Excel & DR) are copied, while hard-coded numbers in the range remain blank to allow user input (e.g., comments).

Dynamic range IDs in the menu help users directly reference these ranges in their formulas.

They can support collaborative data collection processes involving multiple contributors, ensuring seamless integration of inputs.

Set up the Template

Dynamic Ranges require some background work prior configuring it. To set up the template, follow these steps:

  1. Set up the template with the layout you want. For example, List of Accounts, with months from FY2024 in dimensions. 

  2. Create dummy text where the unique list will start. 

  3. Write a formula using a DR.GET function with relative cell references. 
  4. Copy the formula to the first row to ensure the relative references are correct.
  5. The template is now ready to set up the dynamic references.

Create a Dynamic Range

After setting up the template, follow these steps to create a Dynamic Range:

  1. From the Datarails ribbon, select Dynamic Range > Add Dynamic Range.

  2. Give the new Dynamic Range a descriptive name. For example, you can include the field or value being used, and optionally incorporate the tab name.

  3. Click Save to open the Dynamic Range editor.

  4. Configure the Dynamic Range:

    • ID: This field is filled automatically and requires no adjustments.

    • Category: Highlight the placeholder text for the dynamic list and click the down arrow.

    • Formula Range: Highlight the cells with formulas and click the down arrow. If there is a Total column, ensure it is included in the Formula Range.

  5. Click Select next to the Field Selector to open the 'Add Dynamic Range' window:

    • Select the table, and a list of all fields will appear.
    • Drag and drop the relevant fields into the Display Fields section. You can add more than one field, but the number of fields must match the number of columns chosen in the Category section. The order of fields determines their placement: The top field corresponds to the left column, and the bottom field corresponds to the right column.
  6. Add filters as needed to limit unique values.
  7. Click Save to confirm the dynamic references.

  8. Review the settings in the Dynamic Range pane. Optionally, check the Zero Suppression box to exclude rows with null values.
  9. Click Done. An update screen will confirm the range and the number of rows added.
    • Click OK to generate the dynamic range. The list will refresh, and values will appear.
    • Optionally, activate "Don’t show again" to disable this screen in the future. To re-enable it, go to Help > Settings > Disable notifications on Dynamic Range updates.

       

  10. Make any final adjustments to the rows and columns.
  11. Submit the file to save changes.

Manage Dynamic Ranges

Clicking Manage Dynamic Ranges opens a list of all existing Dynamic Ranges. Selecting a range highlights it in the relevant tab. Within this interface, you can:

  1. Edit existing Dynamic Ranges to modify their configurations.
  2. Create new Dynamic Ranges directly from the interface.
  3. Delete or rename Dynamic Ranges as needed.

Dynamic Range with Filters

Dynamic Ranges are dynamic, allowing you to refine your output and adjust results as needed. Adding a filter to the Dynamic Range triggers the feature to update the range of values according to the filtering criteria. For example, filtering for a specific entity, such as CAD, will display only items relevant to CAD. 

  1. Start by adding the relevant filter in the Filters section.

  2. Apply the filter to narrow down the range of values.

  3. Once the filter is applied, an update screen will confirm the range and display the number of rows added. (This screen will appear unless "Don’t show again" is activated in the settings).

  4. Click OK to finalize the updates. The Dynamic Range will now reflect the filtered data.

This functionality ensures that your reports stay accurate and relevant to your specified criteria, without manual adjustments.

Disable Dynamic Ranges notifications

When refreshing the file or applying a filter, an update screen will appear, confirming the range and displaying the number of rows added. To disable this screen, follow these steps:

  1. From the Datarails ribbon, select Help > Settings.
  2. Check the box labeled Disable notifications on Dynamic Range updates and click OK.
  3. To re-enable notifications, repeat the process and uncheck the box.




© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.