Data validation - type a number between two values OR select text from drop down menu

Question

I have an Excel 2013 form with data validation in some cells.

I would like the user to be able to enter a number between 0001 and 9999 (in that format) OR select NOT WEIGHED from a pulldown menu.

I understand how to restrict the number entry alone, and how to allow the restrict the number entry and/or allow the user to type any text, but I know they won't type the required phrase. It would be easier if they can just pick it.

Am I overcomplicating this or have I missed a simple answer?

Thank you

Answer 1

Another approach would be to use 0000 - 9999 and have 0 denote NOT WEIGHED - use the custom number format

0000;0000;"NOT WEIGHED"

and 0 will display as - but not be entered as - "NOT WEIGHED" so any formulas that you use would need to account for 0 meaning that instead of the numeric value 0.

Answer 2

Thank you for that.  Yes, it does seem the most straightforward solution.  My only concern is that the type of users who will be completing this form are likely to regard any pre-filled cells as 'completed' and ignore them, even when the default entry does not apply.

Answer 3

The simplest solution is to fill the cell with NOT WEIGHED first, and use DV to control the number entry.

You could also use the worksheet change event to re-fill the cell with NOT WEIGHED if a valid numeric entry might be cleared by the user.