It is possible to provide default values for whole model elements in Excel files. To do this, the data has to be laid out in the Excel file in a particular way, so that STEM can interpret it correctly.
In general, the appropriate Excel reference corresponds to a 2 × n block of cells, in which the first column (or row) contains n
labels and the second column (row) contains n
values. Alternatively, you can use two references, separated by a comma, corresponding to a pair of 1
×
n blocks of labels and values respectively. These separate 1
×
n
blocks need not be adjacent, so you can arrange data for a number of elements in a table, with a common block of labels down one side.
The labels are the same as those used in internal references, relative to the type of structure being extracted. For example, if a Resource has a formula
'filename'!"labels,values", the strings in the block defined by the name ‘labels’ should be Capacity, Costs.CalibYear, Costs.Capital, etc.
Figure 1: Sample spreadsheet layout for Resource defaults
If the Resource Cost data has a formula (on the button leading to the Costs dialog), the labels should just be CalibYear, Capital, etc.
Figure 2: Sample spreadsheet layout for Cost defaults
Sample labels
A sample spreadsheet is supplied with STEM, called model.xls, which shows all the labels for every field in each type of element, together with a set of named ranges which indicate the appropriate usage. For example, the name ‘Resource’ is defined as a 2
×
n
block of labels and values, which can be used to provide defaults for a Resource, through the formula
'MODEL.XLS'!Resource. Alternative names ‘ResourceLabels’ and ‘ResourceValues’ are defined as 1
×
n
blocks of labels and values respectively, which can be used to provide defaults for a Resource, through the formula
'MODEL.XLS'!"ResourceLabels,ResourceValues". A listing of model.xls is included as an appendix – see 11.1 External Reference Labels for Excel.
When you arrange data in your own spreadsheets for reference in STEM, only those fields you actually require need be included in the specified range and these fields can be presented in any order. If a cell in the labels block is blank, the corresponding value cell is ignored.