The HideRowIf function

The HideRowIf function hides a row in a Wrap when a controlling cell is TRUE. It works like when you hide rows with the Hide Rows/Sheets widget.

The HideRowIf function provides exactly the same functionality as when you hide rows with the Hide Rows/Sheets widget, but implemented as a function. When the controlling cell returns TRUE, the HideRowIf function hides the row it is in. You activate the function by inserting it into any cell on the row you want to hide.

There is a similar function that hides unnecessary columns. There is also a function that locks rows and one that locks columns.

Example: hiding irrelevant input fields

You wouldn’t ask a parent “how old is your third child” if you knew they only have two children. It is a common problem in Wraps and other web forms that some questions only need to be asked under certain conditions. Unless a question is relevant, it is often better to hide it from the user.

To create a Wrap that handles this situation automatically, you can begin by asking the respondent how many children they have. In the example below, their response will appear as a choice from the dropdown widget in cell B2, which is named children_count.

Screenshot of a Wrap with a field to enter the number of children a parent has

We will use rows 3 and down to ask for the age of each existing child. There is one controlling cell in column C for each possible child. The controlling cells must be named since their contents are saved in the database. In this example, the controlling cells are named no_child_1, no_child_2, etc and we expect each controlling cell to be set to TRUE if there is no corresponding child.

Screenshot of a controlling cell with the HideRowIf function

  • You can see in the formula above that the function for no_child_1 in C3 returns TRUE if children_count in B2 is less than one, i.e. the user has no children.
  • An identical function in C4 sets the controlling cell for row 4 to TRUE if there is no second child.
  • For every supported number of children, there is a row in the table, with a controlling cell that must return FALSE if the corresponding child exists and it becomes necessary to ask for his or her age.

We now insert a HideRowIf function into column D on the row for each child. The function in D3 below hides row 3 if children_count in B2 is 0 which causes the controlling cell in C3 to be set to TRUE by the formula in the screenshot above.

Screenshot of a HideRowIf function

If the respondent does have one or more children, children_count will contain 1 or more, C3 will be set to FALSE, and since row 3 is not hidden, the question about the first child’s age will be visible in the Wrap. For each additional child, one more question becomes visible in the list.

One of the advantages of the HideRowIf function is that we can use Excel’s “fill down” feature, or copy/paste, the function call to each row of a table like this. The reference to C3 on row 3 will automatically become a reference to C4 on row 4, and so on. If we instead had used the controlling cell’s name no_child_1 in the first HideRowIf function in D3, all the other rows that we fill or copy it to will also use no_child_1 as the controlling cell, which is not what we wanted in this example.

You may want to hide both the HideRowIf function and its controlling cell from the user. You can do this with the Utility widget.

Function reference

Screenshot of a HideRowIf function

Example

In this example, row 3 of the Wrap will be hidden if the controlling cell in C3 returns TRUE.

This is what this HideRowIf function call will look like:

=HideRowIf(C3)

Format and parameters

=HideRowIf(expression)

Hides the row that the HideRowIf function is placed in, if the expression is True.

The cell containing the HideRowIf function can be hidden or in a hidden column.

expression

Specify the address or name of the controlling cell that returns TRUE when the row is to be hidden. The controlling cell can be on a different row or worksheet than the row it is hiding. The same controlling cell can be used by any number of HideRowIf functions on any number of sheets in the same workbook.

The controlling cell can be hidden, in a hidden row or column, and/or on a hidden sheet.

If the Wrap doesn’t already have a cell that you can use as the controlling cell, you can control the visibility of the row with a condition directly in the HideRowIf function that either returns TRUE or FALSE. If the controlling cell no_child_1 wasn’t already available in the Wrap in the example above, D3 could instead have used the number of children in B2  to control the visibility of row 3:

=HideRowIf(children_count<1)

However, the function above would not fill down or copy/paste nicely. To make it possible to use the same function on all the rows of the table, you could change it to

=HideRowIf(children_count<ROW()-2)

The controlling cell is an input cell and must be named. The cell with the HideRowIf widget does not have to be named, and naming it adds little value or readability.

Known issues

  • You cannot hide a row that has merged cells. If C6:C9 are merged into one cell, you cannot hide any of the rows 6 through 9 because it would include parts of the merged cell. WrapCreator will detect this and issue a warning message. You will have to unmerge the cells or refrain from hiding the column.
  • If there are empty cells in two or more vertically adjacent cells, Excel sometimes merges them. This behavior is something we can detect but not control. If we find that such a spontaneous merge affects a hidden row, we will let you know with a warning message. In this case, you will have to fill one or more of the affected cells with any invisible character, e.g. space, which will prevent Excel from merging the cell.