You have a data file that needs to be updated by either one or several different people. It would make their task and your future task easier if they could pick from a list whilst inputting new data. This way the data is going to be quality controlled by you. To do this we will set up together Validation Lists in Excel.
N.B. This Tip is written in Office 2016 so images of tools or ribbons may differ from your version. The features still work the same.
Start in a new sheet and type out all the different lists you may need in separate blocks.
Use the naming feature for each block.
Now set up your datasheet.
Select the entire column or row where you would like a user to be able to choose from one of your lists that you created earlier.
Select the Data Tab
Select the Data Validation tool
Select List from the drop down menu in this dialog box for the Allow field.
Click in the Source field and either type, highlight or select from a list using F3, the name of the range of the list you created earlier.
Press the F3 key to bring up the list of named ranges your file.
Select the correct one.
Select OK then Ok again.
It may make your life or your users life a little easier to colour the background of the cells that hold a validation list so that they know which cell to go to in order to see a drop down arrow.
Now when you select the cell with the dropdown arrow in the column you have previously selected to use the Validation Rule in you will see a drop down list that you created earlier for just this purpose. You can select one of the items in your list and it will be placed into your cell.
A validation rule can be replicated down the sheet if you use the autofill feature.
