🎓
Models University
  • Alpha Testing
    • 👋Welcome to Alpha
    • Installation and Setup
    • Support and Help
    • Terms and Licence
  • Start Here
    • What is Models?
    • Quick start
    • Tutorials
  • Using Taglo Software
    • The Taglo Interface
      • Navigating in Models
      • List Views
      • Adding Items
      • Deleting Items
      • Searching and Filtering
      • Previewing and Editing
      • Using Wizards
      • Save/Undo/Redo
      • Keyboard Shortcuts
    • Data Storage
    • Namespaces
    • Inbuilt data
  • Tags
    • Introduction to Tags
    • Tag Basics
    • Tags Applet
    • Advanced Tags
  • Models
    • Home Tab
      • Creating a Model
        • Timings
      • Version History
      • Application Settings
    • Model Editor
  • Variables
    • Variable Basics
      • Variable Setup
      • Unit and Calculations
    • Time Segments
      • Time Segment Setup
    • Summaries
    • Default Assumptions
  • Units
    • Introduction to Units
    • Key Unit Concepts
    • Advanced Units
    • Defining a Unit
    • Unit Factors
  • Dimensions
    • Introduction to Dimensions
    • Dimension Setup
    • Defining Unit Variance by Dimension
  • Data Types
    • Data Types
  • Assumptions
    • Introduction to Assumptions
    • Creating/Referencing Assumptions
    • Assumptions Wizard
      • Assumption Dates Step
      • Assumption Values Step
      • Linking to external Data
      • Linking to Excel Inserts
      • Sensitivity Values
  • Calculations
    • Introduction to Calculations
    • The Calculation Page
      • The Formula Entry Bar
      • Unit Analysis
    • Calculation Parts
      • Variable References
        • Referencing Time
        • Referencing Dimensions
        • Lookups
        • Summarising Variables
      • Period References
      • Functions
        • Advanced Functions
  • Aggregation
    • Introduction to Aggregation
    • Creating Aggregations
  • Rearranging
    • Introduction to Rearranging
    • Rearranging Example
    • Rearranging Tips
  • Cases
    • Introduction to Cases
    • Managing Scenarios
    • Managing Sensitivities
  • Layout
    • Generating Excel
    • Auto Layout
    • Sheets
      • Sheet Content
      • Adding and Editing Content
    • Excel Formatting
      • Excel Themes and Styles
      • Line Formatting
    • Data Tables
    • Excel Generation Troubleshooting
  • Excel Inserts and Outputs
    • Excel Inserts
    • Excel Outputs
  • Number Formats
    • Number Formats
  • Dashboards
    • Dashboards Basics
    • Dashboard Layout
    • Dashboard Items
    • Excel Dashboards
  • Metrics
    • Metrics
  • Charts
    • Introduction to Charts
    • Chart Customisation
  • Importing Models
    • Importing Models
  • Data
    • Introduction to Data
    • Creating Data
    • Data Sources
Powered by GitBook
On this page
  • Overview
  • Linking to Insert inputs
  • Example link to Excel Insert
  1. Assumptions
  2. Assumptions Wizard

Linking to Excel Inserts

How to link Assumptions to Excel Inserts

PreviousLinking to external DataNextSensitivity Values

Last updated 10 months ago

Overview

As described in the help sections, you can connect custom Excel sheets to your generated Model, either by creating from your Model, or by linking to your Inserts - this page deals with the second of these.

Generally, we advise against linking Assumptions to Excel Inserts if it can be avoided. If your inputs are produced by a dynamic Excel model, you will not be able to control this model from within the , and there's usually a way to produce whatever inputs you need in Models!

Linking to Insert inputs

Assumptions are linked to Inserts via . You will need to create a Named Range in your Insert that represents your input data, and then tell Models how to link to that Named Range.

The following example illustrates this process.

Example link to Excel Insert

In this example, we have some input data for the New members per month Variable that we want to take from a custom Excel sheet, as shown below. The input data is in a 3x2 table, with one Membership type per row, and one Region per column. This input sheet is contained in an Excel Insert that we have already created, called Volume Input. To make the input data accessible from Models, we must assign a Named Range to it: in this case, we have given it the name Input_NewMembersPerMonth.

To link our data to this input, we select and Edit the relevant Assumptions, as shown below.

This opens an Assumptions dialog. First, we change the source type to Link to Excel Insert (1), which results in several additional steps being shown. In the Link to Insert step, we choose the Volume Input Insert (2), and the Input_NewMembersPerMonth Named Range (3).

Before this point, you should ensure that the linked workbook is not open in Excel, so that Models can successfully access information about its Named Ranges.

Finally, we need to enter some default values for the data (6), so that Models can calculate its values when it does not have access to the Excel Insert (Models will not extract data from the input workbook when re-calculating the Model). Note that in the example below, we have arranged the table with Region on the horizontal axis so that we can copy and paste data directly from the input workbook.

Once you have selected a Named Range, you then need to give Models information about the shape of the data in that Named Range. You do this by matching the layout of data in the input workbook to the displayed table in Models. In the example below, we drag the Region to the Horizontal section (4), which results in the correct table configuration (5).

Dimension
Excel Inserts
Outputs
Assumptions
Model Editor
Named Ranges
Excel Insert with input data
Editing Assumptions
Assumptions dialog: Linking to Excel Insert
Assumptions dialog: Matching shape of input data
Assumptions dialog: Entering default values
Section of an Excel spreadsheet, used as an Excel Insert in Models. A range of cells to be used as an input has been assigned as an Excel Named Range
Screenshot of the Assumptions list, with several assumptions selected, highlighting the Edit button in the preview panel
Screenshots showing how to manipulate Dimensions to correctly link to Excel Insert data inputs
Screenshot showing Default Values page of Assumptions dialog. The user has entered values to use when the Excel Insert input is not available.