Linking to Excel Inserts
How to link Assumptions to Excel Inserts
Last updated
How to link Assumptions to Excel Inserts
Last updated
As described in the Excel Inserts help sections, you can connect custom Excel sheets to your generated Model, either by creating Outputs from your Model, or by linking Assumptions 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 Model Editor, and there's usually a way to produce whatever inputs you need in Models!
Assumptions are linked to Inserts via Named Ranges. 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.
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.
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 Dimension to the Horizontal section (4), which results in the correct table configuration (5).
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.
