How can you sort data validation input list in google spreadsheet

Question

Given the following data validation setup I can only select foods in the way I entered them in the list of foods column. Is there a way to sort these before I'm able to select those?

Answer 1

You can use this formula to automatically sort the data you're entering:

=SORT(UNIQUE(A2:A500))

Enter this formula into another column, or another sheet, and use that range instead as the source for your data validation.

Answer 2

You could use a PivotTable to help you:

1. Select the field containing the list and go to 'Data' > 'Pivot table report...'

2. Click 'Add field' for 'Rows' and pick the field in the dropdown. Ensure that the order is ascending, that the 'Show totals' is unchecked (or else, you'll get 'Grand Total' in your validation) and that the option is to update the table on each change:

Note: You usually get the pivot table in a different sheet. You can copy/paste the data in that new sheet and change the source data by clicking on 'Edit range...' like I did in the picture above, then delete the initial sheet.