Standardized Dropdowns for Dynamic Fields

Suppose you have a spreadsheet to collaborate with colleagues. You've made one field where they can type in whatever they want, but you know that you want some level of standardization, otherwise you can't do aggregate analysis later. What do you do?

The answer is to use Data Validation while simultaneously allowing your users to type in whatever they want. You'll find that if you provide "hints" as to what to type, you'll have an easier time getting people to be consistent about their inputs.

Consider the following example, where we have a small apartment rental company that wants to track claims when tenants need repairs. Here's a link to that spreadsheet.

The spreadsheet without data validation to enforce consistency.

They could allow the field to be open-ended, but- again- that would make it more difficult in the future to analyze incidents in the aggregate someday. Even if it were only being filled out by a few people, you'll run into trouble if you're trying to compare "plumbing", "plumbing problem", and "leak repair". We need to be consistent!

The solution here is to have the data validation dropdown populate with all the previously entered contents. That way you (and your colleagues) won't be compelled to enter more than one entry for reasonably similar incidents and you can catch deviation quickly.

We do this by creating another sheet in the workbook (hidden if you prefer) called Config. In the first column, I'm going to write a function to get the unique values from the tracker sheet. Like this: =UNIQUE(Sheet1!D2:D). Notice it's open ended to capture the entire column.

Capture unique entry values in a configuration sheet.

Next, I'll go back to the tracker sheet and set up data validation for column D that references my unique entries list from the Config sheet.

Data validation on the tracker sheet.

Again, you can see that I've used an open-ended reference to make sure it applies to the whole column after the header.

The finished spreadsheet.

Now you can see the sheet recommends previous values to the user, but still allows them to type in new values as they go. You'll find that this enforces consistency in your spreadsheets whether you have multiple contributors, or you're just forgetful. ;)