Named ranges are a native Excel feature utilized by Datarails to dynamically manage parameters within Excel. Leveraging named ranges helps automate updates when files are cloned or rolled forward, significantly enhancing accuracy, efficiency, and reducing manual adjustments.

The dynamic named ranges listed below will help you transform your planning files into dynamic models, saving you time and reducing manual workload.

To use these named ranges, ensure your file is connected to a Filebox in the Datarails platform (the Submit button should be visible at the top right in the Datarails ribbon). Simply type (=DR_) and the named range list will appear.

Available Named Ranges

DR_scenario

This named range returns the scenario type assigned to the file:

• Returns Forecast for forecast files.

• Returns Budget for budget files.

DR_scenario_cycle

This named range returns the current budget cycle or forecast scenario tag assigned to the file.

Forecast files: returns the scenario cycle (formerly "forecast tag"), such as 0+12, 1+11, etc.

Budget files: returns budget cycle, like Top-down or Bottom-up.

For both budget and forecast files, the returned value does not include the year.

DR_prev_scenario_cycle

This named range retrieves the previous scenario cycle available within your dataset:

• Forecast Files: Returns the last forecast tag used. For example, if forecasting quarterly, a forecast of 6+6 would yield 3+9.

• Initial Forecast Cycle of the Year: If it's the first forecast cycle for the year, it returns the default budget cycle for that year. For example, for a 3+9 cycle in 2025, it returns the default budget cycle for 2025.

• Budget Files: Always returns Actuals.

• Actuals Files: Always returns Actuals.

DR_planning_scenario

Applicable exclusively to forecast files, this named range returns the assigned planning scenario tag for the file.

DR_tag_date

• Budget Files: Points to the last day of December of the relevant year.

• Forecast Files: Points to the end of the first forecast month. For instance, a 4+8 scenario for 2025 returns 5/31/2025.

Example Usage

Creating Dynamic Planning Models

Using these named ranges effectively ensures smooth file rollovers and automatic updates when adjusting to new scenario cycles or planning scenarios.

Examples:

Dynamic Scenario:
Use this formula to display the scenario type (Budget, Actuals, or Forecast) based on the current scenario and actualized months.

=IF(DR_scenario = "budget", "Budget", IF(DR_tag_date > C2, "Actuals", DR_scenario))

Dynamic Scenario Cycle:
Returns the appropriate cycle tag based on the scenario type and whether actuals have passed the reporting date.

=IF(DR_scenario = "budget", DR_scenario_cycle, IF(DR_tag_date > C2, "Actuals", DR_scenario))

Dynamic Formula with DR.GET:

=DR.GET(reporting_month, D$7, scenario_cycle, IF(D6="Actuals", "Actuals", DR_prev_scenario_cycle), item, $C9, planning_scenario, D$8)

Pro Tips

Dynamic Field References:
Use dynamic field references to maintain accuracy, even if fields within dynamic ranges are changed.
The dynamic ranges id can be found in here:

For example, for dynamic range ID 45746 with fields Account and Department:

dr_1234_field_1 = Account
dr_1234_field_2 = Department

Example of usage in DR.GET:

DR.GET(Value,"[Entity]",$J$1,"[Scenario]",J$10,"[Scenario Cycle]",J$9,"[Reporting Month]",J$8, DR_61386_Field_1,$B15,DR_61386_Field_2,$J$3) 

These practices help maintain robust, dynamic, and easily maintainable planning models within Datarails.

Summary

• Named ranges keep your Excel planning files dynamic and up-to-date when rolling forward or cloning.

• Key named ranges like DR_scenario_cycle, DR_prev_scenario_cycle, DR_planning_scenario, and DR_tag_date adapt automatically to your scenario context.

• Using conditional logic in formulas allows your reports to automatically adjust to Budget, Forecast, or Actuals.

• The DR.GET formula with named ranges allows for streamlined, context-aware data pulls.

• Always ensure your file is connected to a Filebox so named ranges can be recognized and function properly.

By applying these tools and practices, you can simplify your planning process, reduce manual work, and ensure your models stay aligned with the latest data.

Updated April 07, 2025 12:17