Down down list in excel

Question

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).

Answer 1

To add a list to a worksheet place the cursor in the required cell and then select Data | Validation | Settings. In the dialog box select 'Allow: List' and ensure the 'In-cell dropdown' box is ticked.

The source data refers to a range of cells containing the selection of options (e.g. H12:H15).

Source

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).