Exercise 18: Linking simple inputs from Excel

Data can be linked from Excel using a formula in STEM which refers to a specific cell or named range in Excel. This kind of relationship is called an external link.

The use of explicit cell references, however, is strongly discouraged, as this kind of link will break if any rows or columns are inserted or deleted before the cell or if sheets are renamed. Instead it is preferable to use a named range in Excel, as explained below. The named range is managed by Excel, which will automatically keep the name with the cell whenever rows and columns are inserted or deleted. By default, Excel names are global (i.e., they include the sheet reference), so the named range stays valid even if sheets are renamed.

Excel

1. Start Excel and save a new workbook as Inputs.xlsx in the same folder as where your training models are saved.
2. Move to Sheet1 if necessary and enter the value 360 into cell C3.
3. Click in the name box, and enter something like MyTariff. Alternatively, you may prefer to use one of the Name commands on the Insert menu in Excel.

4. Save the workbook with this new value before continuing.

Tariffs

5. Go to the Tariffs dialog for the DSL access service and select the Rental Tariff field.
6. Delete the current formula for the tariff, 30 * 12 (but leave the Rental Tariff field selected).

External links

7. Select Links… from the File menu to access the External Links dialog.
8. Click Add to define a new external link, and then click Browse in the Define External Link dialog. STEM should browse directly to the folder where the model was saved.
9. Select your new workbook, Inputs.xlsx, and click Open. The name of the workbook appears in the File Name box, together with the full path.

10. Check Relative Path to remove the path information. This means that STEM will always look for the workbook in the same folder as the model, which means that the whole working model folder can be copied to another machine and everything will still work normally.
11. Click OK to return to the External Links dialog, where Inputs.xlsx will now appear in the Links box.
12. Click Paste. The Select Name dialog appears.

External reference

13. Select MyTariff and press <Enter> or click OK. A properly formed external reference is pasted into the formula bar for the rental tariff (the most recently active data dialog).

14. Press <Enter> again or click the button to enter the formula.
15. The value entered in Excel is now linked into the STEM model.
16. If you subsequently update this value in Excel (and save the workbook), then you can update the values in STEM directly by clicking Update in the External Links dialog. Alternatively, STEM will automatically prompt you to update external links whenever you run the model if the workbook has been modified since the last update.