Setting "Required" for "one of" columns in a group

AlexShevyakov
Participant III

Hi there,

I was wondering if there a way to set REQUIRED flag for a ONE OF several columns in a group. Say I have a table where users provide their weather observations.
There is a set of columns for air, visibility, sea state and ice (if any). Each of these group includes 5-6 columns and I would like to have only ONE OF the column in each group to be mandatory. So, ANY ONE of AIR group, ANY ONE of VISIBILITY group, ANY ONE of SEA STATE etc.
That i, a user cannot leave the entire AIR group blank (set blank by default); at least one observation must be provided.
In GAS I could have achieved this via App Script, however, could not so far find a tool here.
Would appreciate a hint on this.
Thank you,

Solved Solved
0 2 285
1 ACCEPTED SOLUTION

Steve
Participant V

Set the Required? expression to the same for each related column:

AND(ISBLANK([Column1]), ISBLANK([Column2]), ..., ISBLANK([ColumnN]))

Or:

ISBLANK(CONCATENATE([Column1], [Column2], ..., [ColumnN]))

To require a minimum of (e.g.) 3 of the group be completed, instead use:

(
  SUM(
    LIST(
      IF(ISBLANK([Column1]), 0, 1),
      IF(ISBLANK([Column2]), 0, 1),
      ...,
      IF(ISBLANK([ColumnN]), 0, 1)
    )
  )
  < 3
)

View solution in original post

2 REPLIES 2

Steve
Participant V

Set the Required? expression to the same for each related column:

AND(ISBLANK([Column1]), ISBLANK([Column2]), ..., ISBLANK([ColumnN]))

Or:

ISBLANK(CONCATENATE([Column1], [Column2], ..., [ColumnN]))

To require a minimum of (e.g.) 3 of the group be completed, instead use:

(
  SUM(
    LIST(
      IF(ISBLANK([Column1]), 0, 1),
      IF(ISBLANK([Column2]), 0, 1),
      ...,
      IF(ISBLANK([ColumnN]), 0, 1)
    )
  )
  < 3
)

AlexShevyakov
Participant III

@Steve

Thank you Steve. I have updated all the columns in the group - in my case it was AND(FALSE()) expression (because a user has to select YES at least for one observation within a group).

Top Labels in this Space