Example
Prepare the list you want to appear on the dropdown list in say Cells A1 to A20
Highlight the area you want to be validated Say cells D5 to D40
Click on Data from the standard menu
Left click on validation from the list that appears
From the setting menu Left click on the small drop down box under Allow to the left of any value
Left Click on LIST
Left click inside the source box to show the cursor inside the box
Click on Cells in your sheet A1 Then A20 to make the cell array appear on the on the source box (this is the cells you have placed the values you wish to appear on the list), this should appear like so =$A$1:$A$20.
Now click on OK the cells D5 To D40 will be validated to the list you have created when any of the cells are selected a small button will appear at the side of the cells and show your validated list when selected.
After a bit of practice you can take this a bit further no VBA required. Check out my second reply for selecting lists in other sheets