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


=OFFSET(Sheet2!$F$3,0,0,COUNTA(Sheet2!$F:$F),1)

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