Share via

Excel text data validation with option to enter other free text

Anonymous
2014-02-28T10:29:25+00:00

I have a spreadsheet template which I use as the basis for a monthly data recording tool (i.e. I start a new spreadsheet each month).

In one of the fields I record a destination address. In many cases this is a frequently-used address; if this were always the case, I would create a Data Validation rule that picks from a drop-down list.

However, in any given entry I want to be able to either pick from the list of previous addresses (to save time and errors in re-typing an existing address) or to be able to enter a new address if it doesn't already exist on my list. It isn't important that any new address entered has to automatically add to the existing list of addresses; the important thing is to be able to either pick from the existing list or enter new data in the same cell without getting the Data Entry Error message.

Can anyone give me a steer on whether this is possible and, if so, how to achieve it?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments No comments

Answer accepted by question author

  1. Anonymous
    2014-02-28T11:40:57+00:00

    [cut]

    However, in any given entry I want to be able to either pick from the list of previous addresses (to save time and errors in re-typing an existing address) or to be able to enter a new address if it doesn't already exist on my list. It isn't important that any new address entered has to automatically add to the existing list of addresses; the important thing is to be able to either pick from the existing list or enter new data in the same cell without getting the Data Entry Error message.

    Can anyone give me a steer on whether this is possible and, if so, how to achieve it?

    Hi Hebe(GB),

    Try:

    On the Ribbon | Data tab |  Data Validation

    Settings | Data validation settingsError Alert tab | uncheck Show error alert

    ===

    Regards,

    Norman

    70+ people found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Anonymous
    2014-03-03T12:14:08+00:00

    Ah, so simple when you know how! Thanks Norman. I've tested it and it works fine.

    7 people found this answer helpful.
    0 comments No comments