I am creating a table in excel to capture metadata and I want to allow a user to select options from a drop down list(1=yes,2=no,999=missing).
You can then hide the column containing the values.
From the same Data Validation window you can choose an Error Alert to appear if something other than the available values is entered.
Remember, there is nothing to stop a user from removing this validation from the spreadsheet, unless you protect the sheet or cell(s).
In Excel- select the Data header tab Go to Data Validation Settings Allow - List
Source - Select the area of a spreadsheet that has the drop down choices