This will help you do a dynamic name to use on a drop down, a Form Listbox or Form Combobox.
The screenshot almost says it all.
You need 4 steps:
1- Create the name for a column in your Excel file, go to Names > Define in Excel2003 or earlier, or go to Formulas > Name Manager in Excel2007 and later.
2- Use the OFFSET + COUNTA function combination, formula used here is
3- Draw your form listbox or combo box, or just do a regular data validation
4- tell that list (drop box) to use the name you just created.
Now, to test it, add any thing below