Workaround For Manually Entering A Value Into A Cell That Is List Validated - If possible

[Ark68]

I have been using data validation to provide a dropdown list for the users to select a value from in a cell. I understand the purpose of validation ... to restrict the value to only those provided in the defined list of acceptable values.

But I'm faced with a challenge, that I hope someone can suggest a workaround, or alternative approach to what I need to do.

I have a cell that allows the user to select a workorder number from a dropdown. This cell has validation applied to it based on the list of available workorder numbers. But there may be an opportunity where the user needs to enter a workorder number that is not part of that list (yet). As long as the user enters a valid workorder number, the process associated with this "non recorded workorder" would eventually add it to the list after the workorder, identified as "new" information specific to that workorder is processed.

So the issue is, if cell validation is restricting the user to only those values in the list already, how would it be made possible for the user to enter a value not in the list? I was thinking of adding "OTHER" to the list for the user to select, opening up the process of data entry specific to a new workorder number and then submitting it to the database. But the cell still won't accept the new value will it even if the code puts it in the cell (overwriting OTHER). It would I assume if I deleted the validation, but I'd prefer not to do that. Would my only solution be to recreate the validation list, delete the xisting one and replace it with the new one (which would include the added workorder number) before sending the "new" number value to the cell? The bahviour I'm looking at is something similar to the combobox form control.

 

[mumps]

When you create the Data Validation dropdown, click on the "Error Alert" tab and remove the check mark to the left of the "Show error....." line. This will allow you to enter a value that is not in the list of available work order numbers. You could have a macro add this value to the list if doesn't already exist.

 

[Fluff]

Another option, on the Error Alert select either Warning, or Information.
That will warn the user, but still allow them to enter a value.

 

[Ark68]

Thank you folks, your responses provide a far more encouraging solution than I thought I was up against.
With each solution I get I learn a little bit more around the features and parameters that often are left without consideration because of lack of understanding.

 

[Fluff]

Glad we could help & thanks for the feedback