Drop-down from worksheet data validation rules (2024)

Both Google Sheets and Microsoft Excel support a built-in mechanism called data validation rules. Data validation rules allow you to constrain the values that can be entered into a worksheet cell. You can define one or more data validation rules for your worksheet. Typically, you define a separate data validation rule for each column in your worksheet where you need to constrain user entered values.

When you create an AppSheet application from a worksheet having data validation rules, AppSheet will automatically detect these data validation rules and apply them to your AppSheet application. This ensures that data values entered through your AppSheet application conform to the same rules as data values entered directly into your worksheet.

Definea Google Sheets data validation rule

You can define a data validation rule in Google Sheets in two ways.

• Manually type in the allowed values into the validation list.
• Create a new worksheet containing a predefined set of cells that contain the allowed values.

When you have a lot of allowed values, we recommend the second approach. The first approach, manually typing the allowed values, imposes a limit of 256 characters for the entire list.

You can follow the steps listed below to define a data validation rule for your Google worksheet. We refer to the original worksheet containing your application data as the DataSheet. We refer to the new worksheet contained the allowed values as the EnumSheet.

1. Add a new worksheet to your Google workbook to contain the allowed values.

   .

2. Open the EnumSheetand allocate a column for your first set of allowed values.
3. Enter a column header value. For example,Fruitsin cell A1.
4. Enter all of the allowed values into this column. For example:

   

5. In the DataSheetgo the column that will contain the data values and select all of the cells in the column, excluding the first row that contains the header.
6. Go to the Data menu and select DataValidation.
   The Data validationrules panedisplays.
7. Click + Add rule.
8. In the Criteriarow select Dropdown (from a range).
9. In the text field, clickSelect data range.
   The Select a data range dialog opens.
10. With the Select a data range dialog windowopen, click theEnumSheettab to open the EnumSheet.
11. Select therange of cells in the column that contains the allowed values. For example, selectApple, Apricot, Avocado, and so on.
    The Select a data rangedialog should now contain something like EnumSheet!A2:A15. The EnumSheetname appears to the left of the !and the selected allowed values cell range appears to the right.
12. Click OKin the dialog.
    The Data validationrules pane will now reflect the EnumSheetname and cell range you just selected above. These are the allowed values that will be used for validation.
13. Click Done in the Data validation rules pane.
14. Optionally, you can additional columns in the EnumSheetfor additional sets of allowed values. For example, you might add a column for Vegetableallowed values, another for Animalallowed values, and so forth. Repeat steps 2 through 13 for each of your allowed values.
15. Ensure that at least one row in the DataSheetcontains data values. For each data column, choose one of the legal allowed values as the data value.
16. Generate your AppSheet application from the workbook (for example, usethe AppSheet add-on).
17. Only the DataSheet will be added to the app initially. You'll need to manuallyadd the EnumSheet tableto theapp.
    It should show up as a suggestion in the Add data dialog, as shown in the following figure.

    

AppSheet will read the DataSheetto get your data values and the EnumSheetto get your allowed values. Once you have created your app,view the columns to ensure all of the allowed values have been set correctly by AppSheet.

Define a Microsoft Excel data validation rule

You can specify enum values in another sheet when working with Excel worksheets. However, one additional step is required to do this. You must use the Excel Define Name feature to name the enum values contained in the other worksheet. Do this as follows:

1. Enter the enum values in a dedicated column on a separate Excel worksheet as described in steps 1 through 4 above.
2. On the Excel Formulastab, click Define Nameto define a new Name that refers to the worksheet and cells you created in step 1. When the New Namedialogue is displayed, enter a value such as FruitEnumin the Nameattribute. Click the icon to the right of the Refers toattribute. Select the worksheet containing the enum values, and select the range of cells containing the enum values. Click OK.
3. In the Excel DataSheetgo to the column that will contain the enum values and select all of the cells in the column. On the Excel Datatab, click Data Validationand select Data Validationfrom the drop-down. In the Data Validationdialog, select Listfrom the Allowdrop-down. In the Sourcefield, enter =followed by the Name you defined in the previous step. Click OK.
4. Ensure that at least one row in the DataSheetcontains data values. For each enum column, choose one of the legal enum values as the data value.
5. Regenerate the AppSheet application and it will extract the enum values from the enum values column you created in step 1.

6. Once you have created your app, open the Data >Columns pane to ensure all of the enum values have been picked up by AppSheet.