Data Sources for Data Validation, DynaRanges, ComboBoxes, and Lists

image_pdfimage_print

For Data Validation, DynaRanges, ComboBoxes, and Lists, you can use several data sources. The type of data source can be a subset, ODBC query or a formula. You have to ensure that the formula result returns the same structure as e.g. subset formula, i.e. three column matrix with element name in the first column, alias in the second and level/ident in the third. In a sample macro function below my_dr_feeder() function will return tree with Years as root node and QTR1 and QTR2 as children of it, with leaf elements Jan and Apr.

When used as a source of DynaRange:

Properties of a DynaRange based on Formula

Properties of a DynaRange based on Formula

It will produce the following DynaRange when executed:

DynaRange based on Formula in a preview mode used in spreadsheet

DynaRange based on Formula in a preview mode used in spreadsheet

Note: when using a source formula or ODBC query, there is one constraint to consider. If the number of elements in the data set is divisible by three, and the last value is numeric, the corresponding process assumes it is handling a subset-like data structure. It will then only show the first, fourth, seventh, etc. element from the source data set. In some dialogs, you can select the option “Show Alias”. It would then only show the second, fifth, eighth, etc. element as first alias, and the third, sixth, and ninth element etc. as second alias.

image_pdfimage_print
Was this post helpful?
NoYes (-9 rating, 11 votes)
Loading...