Linking a Lookup Table to a Spreadsheet

You link a Lookup Table to a spreadsheet by selecting “MS-Excel” from the Data Source field at the bottom of the Lookup Table dialog:

When you do so, a new tab (Excel) is added to the dialog that allows you to define the properties of the spreadsheet link:

You must first enter the name of a Microsoft Excel spreadsheet file by pressing the Options >> button. This will provide options for either selecting an existing file, or creating (and then selecting) a new file.

Note: If you select a file in the same directory as (or a subdirectory below) your GoldSim .gsm file, GoldSim will subsequently display just a local path. If you select a file in a directory above your .gsm file, it will display the full path.

Once you have selected a file, you can subsequently use the Options >> button to select a different file. You can also use the Options>> button to open the selected file in Excel.

Note: GoldSim supports both .xlsx and .xls Excel files. However, if you have an older version of Excel (prior to Office 2007), you will need to install Microsoft’s Office Compatibility Pack in order to read .xlsx files. Excel 2007 and later support an extended worksheet size (1,048,576 rows by 16,384 columns) than earlier versions (65,536 rows by 256 columns). If you wish to import data from an extended worksheet range into GoldSim, you must use Excel 2007 or newer, and the file format must be .xlsx. Note that GoldSim does not officially support versions of Excel prior to Excel 2003.

Warning: You should never try to open a spreadsheet that is linked to GoldSim while a simulation is running. In addition, prior to running a GoldSim model that is linked to a spreadsheet, all referenced spreadsheets should be closed. If you try to interact with a spreadsheet that is being used by GoldSim during a simulation, all interaction with the spreadsheet is blocked by GoldSim. However, under some circumstances this may not be possible and could lead to errors. Hence, as a general rule, you should not use Excel while a Goldsim model that references Excel is running.

It is easiest to understand how to define the spreadsheet linkage by considering 1-D, 2-D and 3-D tables separately.

• You must specify the Row Variable Starting Cell. This represents the first value of the Row Variable. Row variable data must be contiguous and proceed down in a column.

• You can specify the cell (e.g., A2) directly by typing it into the Starting Cell field. In this case, you must also select the sheet from the Starting Sheet drop-list. Alternatively, you can press the Location… button, which opens the spreadsheet and provides a dialog allowing you to directly select the desired cell.

• Dependent data are assumed to be in the column immediately to the right of the Row Variable column.

• The import of data stops when a cell is encountered that is empty or contains non-numeric data.

• You must specify the Row Variable Starting Cell and the Column Variable Starting Cell. These represent the first value of the Row Variable and Column Variable, respectively. Row variable data must be contiguous and proceed down in a column. Column variable data must be contiguous and proceed to the right across a column. The Column Variable Starting Cell must be above the Row Variable Starting Cell in the spreadsheet.

• You can specify a cell (e.g., B1) directly by typing it into the Starting Cell fields. In this case, you must also select the sheet from the Starting Sheet drop-list. Alternatively, you can press the Location… button adjacent to either field, which opens the spreadsheet and provides a dialog allowing you to directly select the desired cell.

• Dependent data for a particular Row and Column Variable is assumed to be at the intersection of these two variables in the sheet. For example, if the Row Variable Starting Cell was A2 and the Column Variable Starting Cell was in the B1, the dependent variable for the first row and the first column would be imported from B2:

If the Row Variable Starting Cell was specified as A2, the Row Variable values would be imported as 10, 20, 30, 40 and 50. If the Column Variable Starting Cell was specified as B1, the Column Variable values would be imported as 3, 6 ,9, 12, and 15. B2 would be imported as representing the dependent variable when the Row Variable was equal to 10 and the Column Variable was equal to 3.In this file A1 would never be used.

• The import of data stops when a cell is encountered that is empty or contains non-numeric data.

• You must specify the Row Variable Starting Cell, the Column Variable Starting Cell and the Layer Variable Starting Cell. These represent the first value of the Row Variable, Column Variable and Layer Variable, respectively. Row variable data must be contiguous and proceed down in a column. Column variable data must be contiguous and proceed to the right across a column. The Column Variable Starting Cell must be above the Row Variable Starting Cell in the spreadsheet. Layers are represented in separate sheets. The value for the Layer Variable for each sheet must always be in the same cell.

• You can specify a cell (e.g., B1) directly by typing it into the Starting Cell fields. Alternatively, you can press the Location… button adjacent to either field, which opens the spreadsheet and provides a dialog allowing you to directly select the desired cell.

• The Starting Sheet represents the location of the data for the first Layer Variable. Additional Layers are assumed to be to the right of the Starting Sheet.

• The values of the Row and Column Variables must be the same in every Layer sheet. If they are not, GoldSim will not import the table.

• Dependent data for a particular Row and Column Variable in a particular Layer sheet is assumed to be at the intersection of these two variables in the sheet. For example, if the Row Variable Starting Cell was A2 and the Column Variable Starting Cell was in the B1, the dependent variable for the first row and the first column would be imported from B2:

• The import of data stops when a cell is encountered that is empty or contains non-numeric data (where it expects to find a layer number)

Note: When data is imported from Excel into a Lookup Table, it is assumed that the values for the independent and dependent variables in Excel are in the same units as those specified in the Lookup Table dialog.

Note: When data is imported from Excel into a Lookup Table, it will stop the import and report an error if the values of the independent variables do not increase monotonically as you move downward/across the table. That is, unlike the case when you enter the data directly, GoldSim will not automatically sort the entries.

GoldSim automatically imports data from the spreadsheet at the start of a simulation if either 1) any of the properties on the Excel tab have been modified; or 2) the Excel file itself has been changed since the last import. You can also import data manually at any time prior to running a simulation (e.g., so you can view it) by pressing the Import Data button in the Excel tab.

Note: When you link a Lookup Table to a spreadsheet, the Edit Data… button changes to View Data…. If you press this button to access the dialog displaying the data, you will note that they are no longer editable. Once you are linked to a spreadsheet in this way, you can not edit the data manually unless you change Data Source back to “None”. When you do so, GoldSim keeps the imported data and makes it editable.

If you want to ensure that you do not import data from the spreadsheet that has been inadvertently edited since you last imported the data, you can choose to “Lock onto” a spreadsheet file (by checking “Lock onto selected file” from the Options>> button). If you are locked onto a file, GoldSim will not allow the simulation to run if the file has been modified in any way (the file is set to read only when it is locked onto). In order to run a simulation with a changed file, you must first remove the lock (by clearing “Lock onto selected file” from the Options>> button).

The example file (ImportTable.gsm) in the General Examples/LookupTable folder of your GoldSim directory (accessed by selecting File | Open Example... from the main menu) illustrates how the Lookup Table data can be imported from a spreadsheet.