Overview

Conditioning allows you to dynamically hide rows, columns or complete sheets of the questionnaire according to the result of a formula.

Conditioning declaration formulas in the Excel workbook :

GTROWCONDITIONING(anchor_cell; formula; rows to be conditioned)

GTCOLUMNCONDITIONING(anchor_cell; formula; columns to be conditioned)

GTSHEETCONDITIONING(Anchor_cell; formula)

The anchor cell must be in a pattern when you want a conditioning on the rows to be copied to each “range” of the pattern.

The range of rows / columns to be hidden must be hardcoded; it cannot be directly defined by a formula. However, it is possible to hide a dynamic number of rows or columns using a formula. Indeed, if the range to hide contains multiple rows or columns, then GT “slides” the formula, allowing the number of rows/columns to be hidden to vary. This downloadable example demonstrates how it works.

Conditioning :

• hides the cells of the rows/columns or sheets, when the formula returns TRUE
• prohibits the correspondent from entering components in the masked areas (rows, columns or sheets)
• does not make components marked as such and located in hidden areas (rows, columns or sheets) mandatory
• does not hide the objects which are initially located in the rows/columns to be hidden
• is never active in Design (but is active in the GTAnswer visualisation via Design)
• has a formula pointing to cells on the same sheet as the declaration and anchor cell.

Sheet conditioning

All sheets (except sheets already hidden in Design) can be conditioned, including sheets in multi-tabs.

At least one sheet must be displayed in the questionnaire, if the conditionings imply that all sheets should be hidden, one sheet will still be displayed.

The status “Export if page hidden” is taken into account when exporting to Excel.

You can thus define in Design whether a page that is hidden by the conditioning : – will be hidden in the exported Excel workbook – will be very hidden in the exported Excel workbook – will not be present in the exported Excel workbook

It is recommended to set the “Export status if page hidden” to “Very hidden”.

Conditioning of rows and columns

Conditioning on a complete range is possible (for example 1:3 or A:D).

In the case of range conditioning, the conditioning formula is “drawn” on the range of rows and/or columns to be hidden.

=gtrowconditioning(A5;B1>0;4:7)

will hide row 4 if cell B1 is >0 will hide row 5 if cell B2 is >0, etc.

=gtrowconditioning(A5;B$1>0;4:7)

• will hide row 4 if cell B1 is >0 will hide row 5 if cell B1 is >0, etc.

To remove ambiguities in the drawing of formulas for conditioning, it is generally sufficient to check the conditioning formulas actually applied in Design.

By dynamically masking the columns, conditioning makes it easier to manage tables with a variable number of columns (see Examples)

Conditioning and pattern

If the anchor cell is in a pattern, the conditioning will be operational on each row of the pattern (the formula will be drawn/recopied taking into account the absolute and relative ranges).

To hide a pattern completely, simply apply conditioning inside the pattern hiding the row(s) in the pattern with the same condition for all rows.

Hiding rows under a pattern is done naturally by hiding the desired row.

Hiding columns is independent of the pattern and should have an anchor cell outside the pattern.

Images and graphics

Hiding rows/columns allows you to vary the number of series or points displayed in a graph.

This can be used to simulate conditional formatting on the graphs by creating, for example, more series than necessary (on the same data but with different formatting) and hiding the series that you do not want to see.

The number of points is dynamically modifiable via conditioning, but this number will then be the same for all series.

A shaping simulation on a graph will be relatively simple if it applies to the whole series.

In the case of shaping on points of a series, it may be useful to use techniques returning an empty string “” or #NA to visually “mix” the points of several series.

In most cases, the maximum number of series should be planned (as in Excel with a standard chart)

For objects (images and graphics) located after conditioned rows or columns:

• the objects are not resized and therefore are not masked even if the object is entirely contained in a masked row/column.
• objects are moved by hiding rows and columns

If an image is to be hidden when its cell is part of a hidden row or column, then it must have the Excel property ‘Move and resize with cells’.

Conditioning, hiding rows/columns in Design and filters

If any row or column is hidden manually during design (in Design), by conditioning or by the interactive pattern filter, then the row or column is hidden in GTAnswer.

Thus a row hidden in Design will remain hidden at all times.

A row that is hidden by conditioning cannot be displayed by the interactive pattern filter.

A row hidden by the interactive pattern filter cannot be displayed by the conditioning.

Excel-Answer import and conditioning

The Excel import is performed taking into account the hidden rows/columns/sheets (where the user cannot enter values) at the time the import is triggered, the same way it is done for locks.

Nested Conditioning

Two conditionings can be declared on common ranges; the rows/columns will be hidden as soon as one of the conditioning is applied.

Example: If we enter these two conditioning declaration formulas in the workbook =gtrowconditioning(A5;A$1>0;5:5) =gtrowconditioning(A5;B$1>0;4:7)

If cell A1 is >0 or cell B1 is >0, row 5 will be hidden.

Performance

All the conditionings on the rows and columns of a sheet are recalculated according to a dependency manager (and the calculation modifications on the sheet).

All sheet packaging is systematically calculated at each data modification.

The cost of row and column conditioning formulas is roughly equivalent to the cost of the same formulas as conditioning formulas that would be written to cells on the sheet. Sheet conditioning formulas are re-evaluated systematically (i.e. at each user input or sheet change).

Conditionning should therefore only be used in cases of low quantities.

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