Spreadsheet Options – Sheets

Select menu: Tools | Spreadsheet Options (Sheets Tab)

These options affect the whole spreadsheet.

From the menu select Tools | Spreadsheet Options then click the Sheets tab.

Auto update Genstat

When selected, Genstat data are updated from the spreadsheet whenever the input focus is switched to another window or menu. If the spreadsheet is loaded from a file, the entire data set is sent on the first update. Subsequent updates only send modified values. If a new spreadsheet is opened with Genstat data, only the modifications are transmitted. If you disable this option, you must use one of the items on the Spread | Update menu to ensure that changed data values are updated before you use other menus or commands to analyse data.

For very large spreadsheets updating the data to Genstat can be time consuming. The Maximum file size option lets you specify a limit for the size of spreadsheets that allow automatic updating of data to Genstat. Therefore, any spreadsheets larger than that value given will have the automatic updating disabled, and the spreadsheet must then be updated using one of the Spread | Update menu items.

Auto refresh sheet from Genstat

If this is enabled, data in the spreadsheet are updated from the Genstat server whenever changes have been made due to calculations in the server. Newly calculated columns will be automatically be added to the end of the spreadsheet.

Remove empty rows and cols on save

When selected, any empty rows and columns will be removed when saving spreadsheets to files or as Genstat data.

Prompt for update on close

Controls whether a prompt appears if a spreadsheet is closed without updating the server for any changed data values.

Paste missing as asterisk (*)

Missing values (or empty cells in text columns) can be represented as null fields or asterisks (*) when copying cells to the Clipboard. When null fields are written to the Clipboard the <TAB> separators are still included. The appropriate choice will depend on the intended destination of the pasted data; for copying to a spreadsheet via the Clipboard a null field should be used, whereas if the data are being inserted in a text file asterisks may be a better representation, particularly if it is intended for use as a Genstat data file.

An asterisk (*) is displayed in the status bar when this option is selected. The current default is applied to each spreadsheet on opening; subsequent changes to the Options menu do not affect open spreadsheets. However, the keyboard shortcut Ctrl+Shift+F8 can be used to toggle the setting for the current spreadsheet; the presence or absence of an asterisk on the status bar indicates the current state.

Paste with full precision

By default the data pasted to the Clipboard are just what are displayed in the spreadsheet. If the number 1.996745 is displayed with 1 decimal place in a cell, then 2.0 will be put on the Clipboard. With this option turned on, the number in the cell (i.e. in this example 1.996745) will be put on the Clipboard.

Clear selection after operation

Enabling this option will automatically remove spreadsheet selections on completion of editing operations that use the Clipboard (Copy, Cut, Paste and Paste Special).

Show new from clipboard options

Controls whether a dialog specifying the options for creating a spreadsheet from the Clipboard is displayed on the Spread | New | From Clipboard menu item.

Copy dates as displayed

This option is enabled when the Paste with full precision option has been selected. When selected, dates are copied to the Clipboard as displayed within the column (e.g. as text). If this option is not selected, dates are copied to the Clipboard using their numerical data/time values. Genstat stores a date/time value as an 8 byte double precision real value. The integer part of the value is interpreted as the number of days from a base date, and the fractional component as the time during the day, as a fraction of 24 hours (e.g. 0.25 = 6 hours).

Use Excel binary clipboard

Controls how data are copied from Excel to Genstat using the Clipboard. When copying data to the Clipboard within Excel the data are copied as text, exactly as they are currently displayed within Excel. This means that numbers displayed with a fixed number of decimals will be be copied with only that number of decimals, and this can result in the loss of some precision. When this option is selected, Genstat will read the underlying binary values for the data on the Clipboard, and the associated formats (e.g. date format), rather than the text displayed on the Clipboard. Therefore numerical data will be copied with full precision and date/time data will be copied as dates.

Note: Excel has an option to base dates from 1904 rather than 1900. Excel does not pass the base date as part of its BIFF format, and so if creating date columns using the Clipboard when the base date in Excel is 1904, this will produce incorrect dates, and the only way to get correct dates is to use File | Open menu or the Excel import wizard. To create a 1904 base date so that it displays correctly, a value of 1462 must be added to these columns.

Use column numbers in symmetric matrix column headings

If this is ticked, and a symmetric matrix has labels, these will still be used for the row headings, but the column headings will display only the column numbers. This provides a more compact view of the matrix, as more columns will fit within the window. If this is not ticked, the labels will be displayed in both row and column headings.

Maximum columns

This sets the limit on the number of columns that one spreadsheet can contain. You may enter any value between 250 and 50000. The new value will not take effect until you exit Genstat and restart it. However, setting a value much higher than you use will create an unnecessary memory overhead.

Maximum undo operations

Sets the limit on the number of spreadsheet operations that are recorded in the Undo history. Setting this value to 0 will disable Undo for spreadsheets. This reduces the memory used by a spreadsheet and increases the speed of many operations.

Use Import Wizard when opening an Excel file

Controls whether the import wizard is to be used when opening Excel files. This provides you with a step-by-step guide for importing data from the file. If you choose not to use the import wizard, you will instead be able to set all the options via a single menu that appears when you open an Excel file.

View/Edit all SQL statements

Controls whether the SQL statement from any ODBC operation is displayed before being run. The SQL statement may be edited while it is being displayed.

Automatically resize window width

If this is enabled, Genstat will automatically change the width of a window displaying a spreadsheet to show as many columns as possible. If columns are inserted or made wider, the window width will be increased; if they are deleted or narrowed, the window width will be decreased.

Cornell Ecology File extensions

Cornell Ecology format files (also known as Cornell Reduced Condensed Format) are text files in a special format. There is no standard extension for these files, so the user must specify the file extensions they use for this format. The default extensions that are interpreted as being this format are CEP, CRC, SPE and ENV, but this can changed here so that text files with the specified extensions will then be read into a spreadsheet rather than opened into an edit window. Enter the file extensions to be interpreted in Cornell Ecology format, separating them with semicolons (e.g. CEP;CRC;SPE;ENV).

Defaults

Resets the spreadsheet options to their built-in default values.