How can I use a data validation dropdown that allows users to add new numbers?

Question

I want to place a dropdown in a cell in Google Sheets that contains a list of words for users to choose from.

I also want users to be able to ignore the words in the list and instead enter any number.

Currently, when a user inputs a value that is not in the list it displays an error message.

How can I allow users to select from the dropdown or enter a number without triggering an error?

Answer 1

You can do this by using dropdown that uses a range to populate its list.

Any user-entered numbers will be added to the dropdown for future users

Instructions

Assumes your dropdowns are in Sheet1!D2:D. Adjust as necessary.

1. Place your list of items you'd like in your dropdown somewhere in your spreadsheet. For example, in Sheet2!A:A
2. In Sheet2!B1 place a formula that will dynamically create your dropdown list from your list items in Sheet2!A:A combined with a unique list of user-entered numbers. For example,

   ={UNIQUE(A:A)); SORT(UNIQUE(
       FILTER(Sheet1!D2:D, ISNUMBER(Sheet1!D2:D))}

3. Enter the following in your Data validation rule:
   1. Under Criteria select Dropdown (from a range)
   2. In the range place Sheet2!B:B
   3. Under Advanced options > If the data is invalid select Show a warning