Principle

The data in a drop-down list is calculated dynamically from a range in the questionnaire or from the cells corresponding to a header in a pattern. During the calculation, duplicates and blanks are removed. Drop-down lists outside the grids cannot be dynamic.

Important : The calculation of the list is only done when the user scrolls the list (like Excel). This has the following consequences:

• The verification of the constraints of the list can no longer be done at the entry: in Answer, it is done when sending the data, at the same time as the verification of the mandatory constraints. Same for Excel import in Answer.
• We cannot check the constraints during customization.

Example : The list (without manual input) in A1 is fed by B1:B7

Select A and change B1 to B:

Therefore, A no longer belongs to the set of values of the range and since the list cannot be entered, the transmission of the response causes the following box to be displayed:

realization

A drop-down list has three ways of feeding data:

• Fixe : the list is fed manually (via “Data…”) or the list is part of a linked list.
• Range : valid for the grid components, The range is specified in “Data source”. The range must be a vector.
• Pattern header: valid for grid components, the header is specified in “Data source”, Design will check that the header belongs to a pattern.

For Excel import, the ITEMSSRC Flag indicates the data source which can be a range of cells or a header in a pattern.

Two syntaxes are therefore possible:

¤¤Header_Drop-downList;L;ITEMSSRC=Header_belonging_to_a_Pattern_for_the_content_of_the_list

=”¤¤Header_Drop-downList;L;ITEMSSRC=”&GTRANGE(Cell range)

If the GTRANGE function is used when pointing to a pattern, always select at least one additional line below the pattern.

Example :

Designating the source items of the drop-down list by the elements of a pattern allows to customize the content of the drop-down list by the data of a table of the client database.

Note : The function GTRANGE(reference) transforms the reference into a character string during the Design-Excel import.

The gtrange should point to an absolute reference if the drop-down list is in a pattern or if the pointed range is under a pattern.

Application : Restricted list

The content of the drop-down list using the ITEMSSRC flag is completely dynamic. The list is determined by the cells of the vertical or horizontal range or component in a pattern.pointed by ITEMSSRC.

The values in the drop-down list can therefore be derived from formulas in the cells pointed to by the ITEMSSRC flag.

These formulas can return a value or a blank depending on conditions that depend on other components: it is thus possible to have more or less restricted contents of drop-down lists compared with an initial reference list.

Imagine a questionnaire in which the correspondent selects the training they want to attend.

Another component offers him the choice of the training (among those selected) that he wants to attend first. The Excel workbook can be made up as follows:

In the case of this example, the list of trainings isn’t in a reason but this could be the case to make the questionnaire more flexible. In the questionnaire, only the trainings that have been checked will appear in the lower drop-down list.

Application : Customizable linked lists

By using the production of list contents from formulas, it is possible to construct linked drop-down lists whose list contents are customizable (pre-filled at the launch of the questionnaires), in particular if the reference lists are in a pattern. There are mainly two techniques:
– Non-pattern linked list: If the parent list(s) are not in a pattern, an additional column of the reference list is used to produce the list
– Linked list on sorted references: If the reference lists are sorted in the order of the parent axes, we exploit the fact that the data is sorted by determining the contiguous block of cells containing the daughter data

The functioning of the linked lists thus developed has two advantages over the linked lists built in Design:
– Linked lists built in Design are not customizable, therefore static.
– The linked lists built in Design do not allow you to manage two child lists with a parent list (e.g.: the choice of country determines the choice of currency and the choice of regions).

Linked lists customizable out of pattern

As in the previous example concerning training , formulas are used in the cells serving as contents of the drop-down list, returning the desired item from the initial list or an empty string depending on the value selected in a parent drop-down list.
These formulas are calculated from the set of combinations of items in the two lists (link expression)

Here is an example of linked lists (the “linked” content of the two lists could be in a pattern to make the questionnaire more flexible).

This example shows two linked lists but this can be extended to three, four lists, etc. by modifying the formulas to take into account the values selected in all the higher level drop-down lists (a simple method is to concatenate, by adding a specific separator character, the items selected in the higher level drop-down lists)

If a parent list is in a multi-tab, the list of variable elements must be in the multi-tab (or in a potentially hidden tab of the multi-tab group).
In the case of a parent list in a multi-tab, the list can be supplied by a pattern which leaves the maximum number of items free.

The list containing the different reference items is not necessarily sorted.
This way of building linked lists cannot be used when the parent list is in a pattern.

Customizable linked lists on sorted reference lists

When the reference lists are sorted, the range of contiguous cells containing the feeder daughter data for the drop-down list can be “calculated”.

A linked list can then be written as follows using the function GTLIST.

=GTLIST(B13 ; OFFSET(K$1; EQUIV(A4;$J$2:$J$42;0) ; 0 ; COUNTIF($J$2:$J$42;A4) ))

The flags (M, E, …) are modifiable as for any other list and are declared with the header.

As a reminder, the function OFFSET(initial_range; offset_lig_nb; offset_col_nb; final_range_lig_nb; final_range_col_nb) is used to return a range where the number of rows and columns can be modified by calculation.

The second argument of the GTLIST function must return the range, possibly calculated, used to populate the contents of the drop-down list.
In the example provided, the purpose of calculating the range of list elements is to first locate the first row containing the parent list value (MATCH).
Then count the number of lines affected by the parent value (COUNTIF).
Finally return a range starting at the first parent value found and having the number of rows referring to the parent value (COUNTIF calculation).

The list containing the various reference items must be sorted in the order in which the drop-down lists are linked: parent axis then child axis.

The full example can be found in the downloadable documents at the end of this article.

If MATCH and COUNTIF are performed on large ranges (more than a few thousand lines), it will be more efficient to precalculate in a pattern apart from MATCH and COUNTIF on parent values or parent combinations.

Demonstration files

The  GTTut-ListeDeroul.zip‎ file contains the elements necessary to implement a questionnaire that includes all the features presented in this article.

The “Instructions for implementing the drop.docx” file, present in the zip, contains the necessary instructions for the implementation.

Was this article helpful?

0 out Of 5 Stars

5 Stars		0%
4 Stars		0%
3 Stars		0%
2 Stars		0%
1 Stars		0%

5 Submit

How can we improve this article?

How Can We Improve This Article?

Name

Email

Submit