There are situations where user would need to make multiple selections from a long list of options in a SpreadsheetWeb application.
Let’s take the attached workbook used to calculate a patient’s medical risk based on family and own medical conditions. Note that all risk factors in this file were randomly assigned. Patients are asked to fill out this form by selecting which illnesses they or any family members have had. Based on the selections, the model calculates risk factors for the patient. You can download it below.
This is a fairly simple Excel model to build. But the turn this into a web-based form due to the shear number of questions. Each illness is one checkbox control that user selection (TRUE/FALSE) is mapped to each Yes/No cell (see column E).
We developed a feature to simplify the development of these type of web applications by combining all selections into a single control. We added multi-select support to our dropdown and radio button controls.
How Does Multi-Select Option Work?
When enabled, multi-select control maps all selections into a single cell. Let’s take the example of a radio button where you list all illnesses in named range ListFamilyIllnesses (D10:D25). The output of this radio button is named range FamilyIllnesses (E7). In a regular radio button, user can select only one option and it would be written into the FamilyIllnesses range.
When multi-select option is enabled for this radio button, user can select multiple options. All selections would be written into the same cell (FamilyIllnesses) in a specific format with a delimiter character. You can select Semicolon, Asterisk, or Tilde as your delimiter character. If you select Cancer and Ulcers from the list, FamilyIllnesses cell will be populated with Cancer;Ulcers if you select Semicolon delimiter.
It also means you need to update your Excel file to distribute the content of the FamilyIllnesses cell into Yes/No column using a simple formula. You can see the formula used in the image below. It is a combination of SEARCH, IF, and IFERROR formulas to populate each cell next to the illness with Yes or No depending on whether user selects it from the list i.e. FamilyIllnesses cell.
Web Applications with Multi-Select Controls
Only radio button and dropdown controls support multi-select option. You can also apply it to Standard, List, or Icon type radio buttons.
Standard type radio button:
List type radio button:
Icon type radio buttons:
You can also change the placement of option to vertical:
Multi-select option in dropdown control is more useful when there are too many options and the space is limited in your user interface. You can simply select from a long list of options one by one and add them to the list of selections.
The dropdown control will list all of your selections:
Special Scenarios
If there are common words in the option list like “Measles” and “Rubella (German Measles)”, selecting “Rubella (German Measles)” would also trigger the SEARCH formula for “Measles”. To prevent this, you can enhance your formula by adding the delimiter to the end of each search key:
=IFERROR(IF(SEARCH(D30&";",OwnIllnesses&";")>0,"Yes"),"No")
Adding “;”, which is the delimiter used in this application, will ensure that duplicates will not break your formula.
