Drop-down cells

This sample, which is saved as Drop-down cells in the Samples subfolder of the Resolver One folder on your start menu, shows how to constrain the allowed input values for a given cell. It's made up of several simple parts:

• The 'cities' worksheet contains a small list of cities and their timezones relative to GMT. The cities column is the sheet's header column, and the row with the labels 'city' and 'time' is the header row.
• The interface worksheet ('Sheet1') has 2 important cells: B2 and B3. B3's formula is '=timezone(B2)'; and, to ensure that we don't pass bad values to timezone, we have set a validation range on B2. In this case, B2's validation range is set to 'cities!$A$2:$A$6'.
• The 'timezone' function, defined in the pre-constants user code. When this function is called (passing in a city name), it uses the city name to look up and return the appropriate value from the 'time' column on the 'cities' worksheet.

def timezone(city):
    cities = workbook['cities']
    return cities['time', city]

You can edit a cell's validation range from 'Validation...' under the 'Data' menu. While validation ranges will always be stored as absolute references (with dollar signs), it is not necessary to include them yourself: entering 'cities!a2:a6' will have the same effect. It is also possible to validate on the contents of a named range -- just enter that range's name instead of a reference to an explicit cell range.

workbook['cities'].Cells.C4.DropdownItems = ['1', '2', '3']