3

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?

1

1 Answer 1

3

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
1
  • I edited your question to include more detailed steps for inexperienced users. Please correct or rollback any changes you don't agree with. I will remove this comment once you acknowledge it.
    – Blindspots
    Jan 15 at 22:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.