Dropdown from Worksheet Data Validation Rules

 

Spreadsheets have a built-in mechanism called data validation rules to constrain the allowed values in a cell. Utilize this mechanism if you want a column to have an 'enumeration' type, i.e. a dropdown list of allowed values. You can either do this by manually typing in the allowed values into the validation list (not recommended), or selecting a predefined set of cells that already contain the allowed values (recommended).

When you have lots of legal enum values, we suggest creating an additional worksheet in your workbook to contain all of these enum values. The alternative, manually typing in the allowed values into the validation list, imposes a limit of 256 characters for the entire list. For clarity, we will refer to the original worksheet containing your application data as the “DataSheet”. We will refer to the legal enum values worksheet as the “EnumSheet”.

Do the following:

  1. Add a new worksheet to your Google workbook to contain your legal enum values. I am referring to this as the “EnumSheet”.



  2. Open the “EnumSheet” and allocate a column for your first set of valid enum values. 
  3. Enter a column header value. In my case I entered “Fruits” in cell A1.
  4. Enter all of the legal enum values into this column. In my case I entered “Apple” in cell A2, “Apricot” in cell A3, “Avacado” in cell A4, and so forth.



  5. In the “DataSheet” go the column that will contain the enum values and select all of the cells in the column.



  6. Go to the “Data” menu and select “Validation”.
  7. The “Data validation” dialog window will appear. In the “Criteria” row select “List from a range” and then click the “Select data range” button just to the right of the drop down list.



  8. The “What data?” dialog window will appear.



  9. With the “What data?” dialog window still open, click the “EnumSheet” tab to open the “EnumSheet”.
  10. Select the entire range of cells in the column containing your legal enum values. In my case, I selected the cells containing “Apple”, “Apricot”, “Avacado”, etc.



  11. The “What data?” dialog should now contain something like “EnumSheet!A2:A15”. The “EnumSheet” name appears to the left of the “!” and the selected enum values cell range appears to the right.
  12. Click “OK” in the “What data?” dialog.
  13. The “Data validation” window will now reflect the “EnumSheet” name and cell range you just selected above. These are the enum data values that will be used for validation.



  14. Hit Save in the Data validation dialog.
  15. If you have multiple sets of enum values, you can add a column in the "EnumSheet" for each set of enum values. For example, you might add a column for "Vegetable" enum values, another for "Animal" enum values, and so forth. Repeat steps 2 through 14 for each of your enum values.
  16. Ensure that at least one row in the "DataSheet" contains data values. For each enum column, choose one of the legal enum values as the data value.
  17. Generate your AppSheet application from the workbook (use the add-on). AppSheet will read the "DataSheet" to get your data values. It will read the “Enum Sheet” to get your enum values.
  18. Once you have created your app, open the Data>Column Structure tab to ensure all of the enum values have been picked up by AppSheet. They should appear in the “Type Qualifier” column for each of your “Enum” fields.

 

Excel Enum Values in Another Sheet 

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 "Formulas" tab click "Define Name" to define a new Name that refers to the worksheet and cells you created in step 1. When the "New Name" dialgo is displayed enter a value such as "FruitEnum" in the "Name" attribute. Click the icon to the right of the "Refers to" attribute. Select the worksheet containing the enum values, and select the range of cells containing the enum values. Click "OK"
  3. In the Excel “DataSheet” go to the column that will contain the enum values and select all of the cells in the column. On the Excel "Data" tab click "Data Validation" and select "Data Validation" from the drop down list. In the "Data Validation" dialog select "List" from the "Allow" drop down. In the "Source" field enter "=" followed by the Name you defined in the previous step. Click "OK". 
  4. Ensure that at least one row in the "DataSheet" contains 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>Column Structure tab to ensure all of the enum values have been picked up by AppSheet. They should appear in the “Type Qualifier” column for your “Enum” field.
Have more questions? Submit a request

0 Comments

Article is closed for comments.